Structured Query Language/UPDATE 2

Hint: Be careful and deactivate AUTOCOMMIT.

The page in hand offers two additional techniques as an extension to the UPDATE command shown on one of the previous pages:

• Computing values, which are assigned to a column, at runtime.
• Using complex subqueries as search conditions in the WHERE clause.

Evaluate Values at Runtime

The values which are assigned to a column may be computed by a correlated or non-correlated scalar value subquery on the involved table or another one. There are many use cases where this technique is utilized: Increase values linear or in percentage, use values from the same or another table, ... . The situation is similar to that described on the page about the INSERT command.

-- The average weight of all persons is stored in column 'weight' of the first four persons.
UPDATE person SET
  -- dynamic computation of a value
  weight = (SELECT AVG(weight) FROM person)
  --  weight = (SELECT * FROM (SELECT AVG(weight) FROM person) tmp) -- MySQL insists on using an intermediate table
WHERE  id < 5;

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

The subquery may use the values of the row, which is currently updated. In the next example, persons receive the mean weight of their family. To compute this mean weight, it is necessary to use the column 'lastname' of the actual processed row.

-- The subquery is a 'correlated' scalar value subquery.
UPDATE person p SET
  -- 'p.lastname' refers to the lastname of the actual row. The subquery bears all rows in mind, not only those with 'id >= 5'.
  weight = (SELECT AVG(weight) FROM person sq WHERE sq.lastname = p.lastname)
  -- A hint to MySQL users: MySQL does not support UPDATE in combination with a correlated subquery
  -- to the same table. Different tables work. MySQL has a different, non-standard concept: multi-table update.
WHERE  id >= 5;

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

Subqueries in WHERE Clause

The WHERE clause determines which rows of a table are involved by the UPDATE command. This WHERE clause has the same syntax and semantic as the WHERE clause of the SELECT or DELETE command. It may contain complex combinations of boolean operators, predicates like ANY, ALL or EXISTS, and recursively subqueries as described in SELECT: Subquery.

-- UPDATE rows in the 'person' table based on the results of a subquery to the 'contact' table.
-- In the example, persons with more than 2 contact information are affected.
UPDATE person
SET    firstname = 'Has many buddies'
WHERE  id IN
  (SELECT person_id
   FROM   contact
   GROUP BY person_id
   HAVING count(*) > 2
  );

-- Check the result
SELECT * FROM person;

-- revoke the changes
ROLLBACK;

The command performs an UPDATE in the table person, but the affected rows are identified by a subquery in table contact. This technique of grabbing information from other tables offers very flexible strategies to modify the data.

It is no error to select 0 rows in the subquery. In this case, the DBMS executes the UPDATE command as usual and throws no exception. (The same holds true for subqueries in SELECT or DELETE statements.)

Exercises

Assign the firstname 'Short firstname' to all persons which have a firstname with less than 5 characters.

Click to see solution

-- Hint: Some implementations use a different function name: length() or len().
UPDATE person
SET    firstname = 'Short firstname'
WHERE  character_length(firstname) < 5;

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;

Assign the firstname 'No hobby' to all persons which have no hobby.

Click to see solution

UPDATE person
SET    firstname = 'No hobby'
WHERE  id NOT IN
  (SELECT person_id
   FROM   person_hobby
  );

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;

Assign the firstname 'Sportsman' to all persons performing one of the hobbies 'Underwater Diving' or 'Yoga'.

Click to see solution

UPDATE person
SET    firstname = 'Sportsman'
WHERE  id IN
  -- The subquery must join to the table 'hobby' to see their column 'hobbyname'.
  (SELECT ph.person_id
   FROM   person_hobby ph
   JOIN   hobby        h  ON ph.hobby_id = h.id
   AND    h.hobbyname IN ('Underwater Diving', 'Yoga')
  );

-- Check the result
SELECT * FROM person;
 
-- revoke the changes
ROLLBACK;