SQL UPDATE

In this tutorial, you will learn how to use the SQL UPDATE statement to modify data of the existing rows a table.

Introduction to the SQL UPDATE statement

To change existing data in a table, you use the UPDATE statement. The following shows the syntax of the UPDATE statement:

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

In this syntax:

• First, indicate the table that you want to update in the UPDATE clause.
• Second, specify the columns that you want to modify in the SET clause. The columns that are not listed in the SET clause will retain their original values.
• Third, specify which rows to update in the WHERE clause.

The UPDATE statement affects one or more rows in a table based on the condition in the WHERE clause.  For example, if the WHERE clause contains a primary key expression, the UPDATE statement changes one row only.

However, any row that causes the condition in the WHERE to evaluate to true will be modified. Because the WHERE clause is optional, therefore, if you omit it, the all the rows in the table will be affected.

SQL UPDATE statement examples

We will use the employees and dependents table to demonstrate the UPDATE statement.

SQL UPDATE one row example

Suppose the employee id 192 Sarah Bell changed her last name from Bell to Lopez and you need to update her record in the  employees table.

To update Sarah’s last name from  Bell to Lopez, you use the following UPDATE statement:

UPDATE employees 
SET 
    last_name = 'Lopez'
WHERE
    employee_id = 192;

The database system updated value in the last_name column and the row with  employee_id 192.

You can verify it by using the following SELECT statement.

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    employee_id = 192;

SQL UPDATE multiple rows example

Now, Nancy wants to change all her children’s last names from Bell to Lopez. In this case, you need to update all Nancy’s dependents in the dependents table.

Before updating the data, let’s check the dependents of Nancy.

SELECT
    *
FROM
    dependents
WHERE
    employee_id = 192;

To update the last names of Nancy’s dependents, you use the following UPDATE statement.

UPDATE dependents 
SET 
    last_name = 'Lopez'
WHERE
    employee_id = 192;

SQL UPDATE with subquery example

Sometimes when employees change their last names, you update the  employees table only without updating the dependents table.

To make sure that the last names of children are always matched with the last name of parents in the  employees table, you use the following statement:

UPDATE dependents
SET last_name = (
    SELECT
        last_name
    FROM
        employees
    WHERE
        employee_id = dependents.employee_id
);

Because the WHERE clause is omitted, the UPDATE statement updated all rows in the dependents table.

In the SET clause, instead of using the literal values, we used a subquery to get the corresponding last name value from the  employees table.