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 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.

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.

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.

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

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