UPDATE statement page 2
Transact-SQL UPDATE statement extends the Standard at the cost of using the optional FROM clause. This clause specifies a table that is used to provide the criteria for the update operation. This extension gives additional flexibility specifying a join that can be used instead of a subquery in the WHERE clause to identify rows to be updated.
Let us write "No PC" in the type column for those PC models in the Product table that have not corresponding rows in the PC table.
The solution through table join may be written as:
Here, we use outer join that results in the pc.model column contains NULL values for those PC models that are absent from the PC table, which is used for providing the criteria for the update operation. Clearly, this task has also a "standard" solution:
The MySQL UPDATE statement also has functionality similar to the optional FROM clause in SQL Server. But instead of FROM clause, the tables are joined directly in the UPDATE clause. Example 6.1.2 in MySQL syntax can be rewritten as follows