Update

To update (modify) data in a table the UPDATE statement is used. UPDATE can be used in two ways:

1. To update specific rows in a table

2. To update all rows in a table

The basic format of an UPDATE statement is made up of three parts:

1. The table to be updated

2. The column names and their new values

3. The filter condition that determines which rows should be updated

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Update Single Column

Assume customer 1000000005 has no e-mail address on file and now has an address, and so that record needs updating. The following statement performs this update:

UPDATE Customers
SET cust_email = "[e-mail]"
WHERE cust_id = "1000000005";

If you don't use WHERE clause, all the rows will be updated.

Update Multiple Columns

UPDATE Customers
SET cust_contact = 'Sam Roberts', cust_email = '[e-mail]'
WHERE cust_id = '1000000006';

Update Under Different Conditions

UPDATE salary
SET sex = (CASE sex WHEN 'f' THEN 'm' WHEN 'm' THEN 'f' END);

Cross-table Update: UPDATE JOIN

If we need information in another table when we update values, we need to JOIN the tables.

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

In the following code, we want to update salary in employees table and we need percentage in merits table.

UPDATE employees
    INNER JOIN merits 
    ON employees.performance = merits.performance 
SET 
    salary = salary + salary * percentage;

If join three tables:

UPDATE TABLE_A a 
    JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b 
    JOIN TABLE_C c ON [condition]
SET a.column_c = a.column_c + 1