Share this page:

SQL: UPDATE Statement

Learn how to use the SQL UPDATE statement with syntax, examples, and practice exercises. Notice that there are 3 ways to write a SQL UPDATE statement.

Description

The SQL UPDATE statement is used to update existing records in the tables.

Syntax

The syntax for the SQL UPDATE statement when updating one table is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE conditions;

OR

The syntax for the SQL UPDATE statement when updating one table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
WHERE conditions;

OR

The syntax for the SQL UPDATE statement when updating multiple tables (not permitted in Oracle) is:

UPDATE table1, table2, ... 
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE table1.column = table2.column
AND conditions;

Parameters or Arguments

column1, column2 are the columns that you wish to update.

expression1, expression2 are the new values to assign to the column1, column2. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

conditions are the conditions that must be met for the update to execute.

Example - Update single column

Let's look at an example showing how to use the SQL UPDATE statement to update a single column in a table.

UPDATE suppliers
SET supplier_id = 50001
WHERE supplier_name = 'Apple';

This SQL UPDATE example would update the supplier_id to 50001 in the suppliers table where the supplier_name is 'Apple'.

Example - Update multiple columns

Let's look at an UPDATE example that shows how to update more than one column in a table.

UPDATE suppliers
SET supplier_name = 'Apple',
    product = 'iPhone'
WHERE supplier_name = 'RIM';

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This SQL UPDATE statement example would update the supplier_name to "Apple" and product to "iPhone" where the supplier_name is "RIM".

Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table.

UPDATE customers
SET c_details = (SELECT contract_date
                 FROM suppliers
                 WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id < 1000;

This UPDATE example would update only the customers table for all records where the customer_id is less than 1000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the contract_date from the suppliers table would be copied to the c_details field in the customers table.

Example - Update multiple tables

Let's look at an UPDATE example that shows how to update multiple tables in an UPDATE statement. (Please note that this syntax is not valid in Oracle).

UPDATE suppliers, contacts
SET suppliers.status = 'Active', contacts.note = 'Also Supplier'
WHERE suppliers.supplier_id = contacts.contact_id;

This UPDATE example would update columns in both the suppliers and contacts tables. When the supplier_id matches the contact_id, the status column in the suppliers table would be updated to 'Active' and the note column in the contacts table would be updated to 'Also Supplier'.

Example - Using EXISTS Clause

You can also perform more complicated updates using the UPDATE statement.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the SQL UPDATE statement, you can use the SQL EXISTS clause.

For example:

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

Or you could rewrite this UPDATE statement using the UPDATE syntax that allows multiple tables as follows:

UPDATE suppliers, customers
SET suppliers.supplier_name = customers.customer_name
WHERE suppliers.supplier_id = customers.customer_id;

In this SQL UPDATE example, whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer_name from the customers table.

Practice Exercise #1:

Based on the suppliers table populated with the following data, update the city to "Santa Clara" for all records whose supplier_name is "NVIDIA".

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5004, 'NVIDIA', 'New York');

Solution for Practice Exercise #1:

The following SQL UPDATE statement would perform this update in SQL.

UPDATE suppliers
SET city = 'Santa Clara'
WHERE supplier_name = 'NVIDIA';

The suppliers table would now look like this:

SUPPLIER_ID SUPPLIER_NAME CITY
5001 Microsoft New York
5002 IBM Chicago
5003 Red Hat Detroit
5004 NVIDIA Santa Clara

Practice Exercise #2:

Based on the suppliers and customers table populated with the following data, update the city in the suppliers table with the city in the customers table when the supplier_name in the suppliers table matches the customer_name in the customers table.

CREATE TABLE suppliers
( supplier_id number(10) not null,
  supplier_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'NVIDIA', 'LA');


CREATE TABLE customers
( customer_id number(10) not null,
  customer_name varchar2(50) not null,
  city varchar2(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'Microsoft', 'San Francisco');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'IBM', 'Toronto');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'Red Hat', 'Newark');

Solution for Practice Exercise #2:

The following SQL UPDATE statement would perform this update in SQL.

UPDATE suppliers
SET city = (SELECT customers.city
            FROM customers
            WHERE customers.customer_name = suppliers.supplier_name)
WHERE EXISTS (SELECT customers.city
              FROM customers
              WHERE customers.customer_name = suppliers.supplier_name);

The suppliers table would now look like this:

SUPPLIER_ID SUPPLIER_NAME CITY
5001 Microsoft San Francisco
5002 IBM Toronto
5003 Red Hat Newark
5004 NVIDIA LA