Update
To update (modify) data in a table the UPDATE
statement is used. UPDATE
can be used in two ways:
To update specific rows in a table
To update all rows in a table
The basic format of an UPDATE
statement is made up of three parts:
The table to be updated
The column names and their new values
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 = "kim@thetoystore.com"
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 = 'sam@toyland.com'
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
Last updated
Was this helpful?