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 values of the row, which is actually 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 - in a recusive manner - subquerys 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.)
Assign the firstname 'Short firstname' to all persons which have a firstname with less than 5 characters.
-- 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.
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'.
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;