UPDATE statement

UPDATE statement changes existing data in a table.

Syntax 

UPDATE < table name >
SET {< column name > = {< expression >
| NULL
| DEFAULT},...}
[ {WHERE < predicate >}]

With a single UPDATE statement, arbitrary number of columns can change their values. However a column cannot be changed in the same UPDATE statement more than once. All rows of a table will be affected if a WHERE clause is eliminated from an UPDATE statement.

If the column is defined to allow NULL values, NULL can be specified explicitly. Moreover, existing value can be changed by default value (DEFAULT keyword is used) for a given column.

An expression may refer to current values in a table to be changed. For example, we may decrease prices by 10 percent on all the laptops with the following statement:

UPDATE Laptop
SET price = price*0.9;

The values of one column are allowed to assign to another column. For example, it is required to change HD less than 10 Gb in laptops. New HD capacity must be one-half of available RAM. The solution may be as follows:

UPDATE Laptop
SET hd = ram/2
WHERE hd < 10;

Clearly, data types of the hd and ram columns need to be compatible. The CAST expression (item 5.9) may be used for conversion.

If the data needs to be changed depending on contents of some column, we can use the CASE expression (item 5.10). Say, if it is needed to setup HD with capacity of 20 Gb in laptops with RAM less than 128 Mb and HD with capacity of 40 Gb in remaining laptops, we can write the query:

UPDATE Laptop
SET hd = CASE
WHEN ram < 128
THEN 20
ELSE 40
END;

Use of subquery is also allowed to define the column value. For example, we need to complete all laptops with most fast available processor. Then we write:

UPDATE Laptop
SET speed = (SELECT MAX(speed)
FROM Laptop
);

Some words about autoincrementable columns ought to be said. If the code column in the Laptop table is defined as  IDENTITY(1,1), then the following statement 

UPDATE Laptop
SET code = 5
WHERE code = 4;

will not be executed, since autoincrementable column does not allow modification, but execution error will be returned. To solve above task, we can proceed as follows. At first, let us insert the needed row using SET IDENTITY_INSERT, then delete the old row:

SET IDENTITY_INSERT Laptop_ID ON;
INSERT INTO Laptop_ID(code, model, speed, ram, hd, price, screen);
SELECT 5, model, speed, ram, hd, price, screen
FROM Laptop_ID WHERE code = 4;
DELETE FROM Laptop_ID
WHERE code = 4;

Clearly, another row with the value code=5 must not be in the table.

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.

Example 6.2.1

The solution through table join may be written as:

UPDATE Product
SET type = 'No PC'
FROM Product pr LEFT JOIN
PC on pr.model=PC.model
WHERE type = 'pc' AND
PC.model IS NULL;

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:

UPDATE Product
SET type = 'No PC'
WHERE type = 'pc' AND
model NOT IN (SELECT model
FROM PC
);

MySQL

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   

UPDATE Product pr
    LEFT JOIN PC ON pr.model=PC.model
    SET type = 'No PC'
    WHERE type = 'pc'
    AND PC.model IS NULL;

Suggested exercises: 7, 9, 12, 15, 17, 20