SQL UPDATE Statement

Summary: in this tutorial, you will learn how to use the SQL UPDATE statement to modify one or more rows in a table.

Introduction to the SQL UPDATE statement #

In SQL, you use the UPDATE statement to modify data of one or more rows in a table.

Here’s the syntax of using the UPDATE statement:

UPDATE table_name
SET
  column1 = value1,
  column2 = value2
WHERE
  condition;Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify the name of the table in which you want to update data in the UPDATE clause.
Second, list the column names and new values in the SET clause. The columns that do not appear in the SET clause will retain their original values.
Third, specify a condition in the the WHERE clause to identify the rows for updating.

The WHERE clause is optional. If you omit the WHERE clause, the UPDATE statement will update all the rows in the table:

UPDATE table_name
SET
  column1 = value1,
  column2 = value2;

SQL UPDATE statement examples #

We’ll use the dependents and employees tables from the sample database to demonstrate the UPDATE statement:

SQL UPDATE Statement - employees & dependents tables

If you have not followed the INSERT tutorial, you need to run the following statement before continuing the tutorial:

INSERT INTO
  dependents (first_name, last_name, relationship, employee_id)
VALUES
  ('Cameron', 'Bell', 'Child', 192),
  ('Michelle', 'Bell', 'Child', 192);Code language: SQL (Structured Query Language) (sql)

Updating one row in a table #

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:

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

Output:

 employee_id | first_name | last_name
-------------+------------+-----------
         192 | Sarah      | Bell

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

Try it

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

To verify the update, you can use the following SELECT statement:

SELECT
  employee_id,
  first_name,
  last_name
FROM
  employees
WHERE
  employee_id = 192;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id | first_name | last_name
-------------+------------+-----------
         192 | Sarah      | Lopez

Updating multiple rows in a table #

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

Try it

 dependent_id | first_name | last_name | relationship | employee_id
--------------+------------+-----------+--------------+-------------
           32 | Cameron    | Bell      | Child        |         192
           33 | Michelle   | Bell      | Child        |         192Code language: plaintext (plaintext)

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

Try it

 dependent_id | first_name | last_name | relationship | employee_id
--------------+------------+-----------+--------------+-------------
           32 | Cameron    | Lopez     | Child        |         192
           33 | Michelle   | Lopez     | Child        |         192Code language: plaintext (plaintext)

Using SQL UPDATE statement with a subquery #

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

To ensure that the last names of children are them same as the last name of parents in the employees table, you use the following UPDATE statement:

UPDATE dependents
SET
  last_name = (
    SELECT
      last_name
    FROM
      employees
    WHERE
      employee_id = dependents.employee_id
  );Code language: SQL (Structured Query Language) (sql)

Try it

How the query works:

First, use a subquery to get the last names from the employees table and use them in the SET clause for updating last names in the dependents table.
Second, omit the WHERE clause to update all the rows in the dependents table.

SQL UPDATE

Introduction to the SQL UPDATE statement #

SQL UPDATE statement examples #

Updating one row in a table #

Updating multiple rows in a table #

Using SQL UPDATE statement with a subquery #

Summary #

Quiz #

Databases #