SQL UPDATE Statement

Summary: in this tutorial, you will learn how to use SQL UPDATE statement to modify existing data in a table.

SQL UPDATE syntax

The UPDATE statement changes existing data in one or more rows in a table. The following illustrates the syntax of the UPDATE statement:

UPDATE table
SET 
    column1 = new_value1,
    column2 = new_value2,
    ...
WHERE
    condition;Code language: SQL (Structured Query Language) (sql)

To update data in a table, you need to:

• First, specify the table name that you want to change data in the UPDATE clause.
• 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 WHERE clause is optional. If you omit the WHERE clause, 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 employees table:

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 employees table.

The record of Janet in the employees before updating is as follows:

SELECT 
    employeeid, lastname, firstname
FROM
    northwind_bk.employees
WHERE
    employeeid = 3;Code language: SQL (Structured Query Language) (sql)

The following statement changes Janet’s last name from Levering to Hill:

UPDATE employees 
SET 
    lastname = 'Hill'
WHERE
    employeeID = 3;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

SQL UPDATE multiple rows

The following 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;Code language: SQL (Structured Query Language) (sql)

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:

SELECT 
    employeeid, COUNT(orderid)
FROM
    orders
WHERE
    shippeddate IS NOT NULL
GROUP BY employeeid
HAVING COUNT(orderid) > 100;Code language: SQL (Structured Query Language) (sql)

The following query increases the salary of the best sale persons by 5%. The best sale person ids are provided by a subquery.

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)Code language: SQL (Structured Query Language) (sql)

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.