SQL - UPDATE Statement

CIW Course in a Nutshell

SQL, UPDATE Statement

The UPDATE statement is the SQL way of modifying a record in a database table.

UPDATE MyTable
SET Forename = 'Frederick'
WHERE ID = 4

The UPDATE clause specifies the table name you are about to modify. The SET clause contains one or more expressions to change individual fields in the table. Lastly, and probably most importantly, the WHERE clause will determine which records to modify.

UPDATE MyTable
SET Firstname = 'Frederick', Lastname = 'Forsyth'
WHERE ID = 4

Had I omitted the WHERE clause in the above example, every record in the table would have been updated to being Frederick Forsyth.

Update FROM

Similarly to the INSERT statement we can use data from another table or query as the source for the UPDATE.

UPDATE titles
SET ytd_sales = titles.ytd_sales + sales.qty
FROM titles, sales
WHERE titles.title_id = sales.title_id AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales)

It should be noted that the WHERE clause applies to the FROM clause and not to the UPDATE clause. When using this syntax, you are updating ALL rows in the target table.