Summary: in this tutorial, you will learn how to use SQL UPDATE statement to modify existing data in a table.
SQL UPDATE syntax
UPDATE statement changes existing data in one or more rows in a table. The following illustrates the syntax of the
UPDATE table SET column1 = new_value1, column2 = new_value2, ... WHERE condition;
To update data in a table, you need to:
- First, specify the table name that you want to change data in the
- Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,).
- Third, specify which rows you want to update in the WHERE clause. The
WHEREclause is optional. If you omit the
WHEREclause, all rows in the table will be updated.
The database engine issues a message specifying the number of affected rows after you execute the statement.
SQL UPDATE statement examples
Let’s take a look at some examples of using
UPDATE statement with the
SQL UPDATE one column example
Suppose Janet, who has employee id 3, gets married so that you need to change her last name in the
The record of Janet in the employees before updating is as follows:
SELECT employeeid, lastname, firstname FROM northwind_bk.employees WHERE employeeid = 3;
The following statement changes Janet’s last name from Levering to Hill:
UPDATE employees SET lastname = 'Hill' WHERE employeeID = 3;
Execute the SELECT statement above again to verify the change:
SQL UPDATE multiple columns
For example, Janet moved to a new house, therefore, her address changed. Now, you have to change it in the
employees table by using the following statement:
UPDATE employees SET address = '1300 Carter St', city = 'San Jose', postalcode = 95125, region = 'CA' WHERE employeeID = 3;
SQL UPDATE multiple rows
UPDATE statement increases the salary by 2% for employees whose salary is less than $2000:
UPDATE employees SET salary = salary * 1.02 WHERE salary < 2000;
Thee employees whose salary is less than 2K received the promotion.
SQL UPDATE from SELECT
The following query selects sales person who has was in charge of more than 100 orders:
Code language: PHP (php)
SELECT employeeid, COUNT(orderid) FROM orders WHERE shippeddate IS NOT NULL GROUP BY employeeid HAVING COUNT(orderid) > 100;
The following query increases the salary of the best sale persons by 5%. The best sale person ids are provided by a subquery.
Code language: PHP (php)
UPDATE employees SET salary = salary * 1.05 WHERE employeeid IN (SELECT employeeid FROM (SELECT employeeid, COUNT(orderid) FROM orders WHERE shippeddate IS NOT NULL GROUP BY employeeid HAVING COUNT(orderid) > 100) bestsalesperson)
In this tutorial, we have shown you how to use the SQL UPDATE statement to change data in one or more rows in a table.