How to Update from Select in SQL

The basic UPDATE statement in SQL allows us to update data in a table.

But what if we want to update data in one table based on data in another table?

There are a few ways to do that in different versions of SQL.

Let’s take a look at the SQL Update from Select techniques in this guide.

Basic Update Statement

To update data in a table, we can run an UPDATE statement. The syntax of an update statement is this:

UPDATE table
SET column = value
WHERE condition;

You can specify one table and one or more pairs of columns and values. You can also specify a condition in the WHERE clause so that only matching rows are updated.

I’ve written about this in my guide to the SQL Update statement.

So what are we trying to achieve here?

 

SQL Update From Select

The problem with the simple UPDATE statement is that it can only take a single table.

Why would we want to include a second table?

We may have a column in one table but the values for that are derived or calculated from data in another table.

Or you may have loaded data from an external source into one table, and want to get some of this data into your “main tables”.

We can do this in SQL. The methods to do this are different between Oracle, SQL Server, MySQL, and PostgreSQL.

Also, you may want to test the performance of each of these “SQL update from select” methods. Some methods may be much faster than others, as they depend on your tables and the quantity of data.

Let’s take a look.

 

Prepare Our Sample Data

We’ll prepare some sample data to use in our examples.

We have two tables:

• person: stores information about people that are in the system
• account: stores account details that are created for people

Here are our SQL statements to create the tables. The sample code is similar for each database, but the data types are different.

Download the SQL file with this sample data and all of the Update methods on my GitHub repository here.

 

Oracle

CREATE TABLE person (
  person_id NUMBER,
  first_name VARCHAR2(100),
  account_number NUMBER
);

CREATE TABLE account (
  account_id NUMBER,
  account_number NUMBER,
  person_id NUMBER
);

INSERT INTO person (person_id, first_name)
VALUES (1, 'John');
INSERT INTO person (person_id, first_name)
VALUES (2, 'Sarah');
INSERT INTO person (person_id, first_name)
VALUES (3, 'Mark');
INSERT INTO account (account_id, account_number, person_id)
VALUES (1, 100298, 2);
INSERT INTO account (account_id, account_number, person_id)
VALUES (2, 103557, 3);
INSERT INTO account (account_id, account_number, person_id)
VALUES (3, 108956, 1);
INSERT INTO account (account_id, account_number, person_id)
VALUES (4, 109703, 4);

 

SQL Server, MySQL

CREATE TABLE person (
  person_id INT,
  first_name VARCHAR(100),
  account_number INT
);

CREATE TABLE account (
  account_id INT,
  account_number INT,
  person_id INT
);

INSERT INTO person (person_id, first_name) VALUES
(1, 'John'),
(2, 'Sarah'),
(3, 'Mark');
INSERT INTO account (account_id, account_number, person_id) VALUES
(1, 100298, 2),
(2, 103557, 3),
(3, 108956, 1),
(4, 109703, 4);

 

PostgreSQL

CREATE TABLE person (
  person_id INTEGER,
  first_name CHARACTER VARYING(100),
  account_number INTEGER
);

CREATE TABLE account (
  account_id INTEGER,
  account_number INTEGER,
  person_id INTEGER
);

INSERT INTO person (person_id, first_name) VALUES
(1, 'John'),
(2, 'Sarah'),
(3, 'Mark');
INSERT INTO account (account_id, account_number, person_id) VALUES
(1, 100298, 2),
(2, 103557, 3),
(3, 108956, 1),
(4, 109703, 4);

 

Sample Data Tables

Here’s what the sample data looks like when we select from it.

Person

person_id	first_name	account_number
1	John	(null)
2	Sarah	(null)
3	Mark	(null)

Account

account_id	account_number	person_id
1	100298	2
2	103557	3
3	108956	1
4	109703	4

We can see that the account_number field in the person table is NULL.

Our examples in this guide will show you how to update the person.account_number field based on the related account.account_number field.

 

1 – Update with From Join

Works with: SQL Server (not MySQL, Oracle, PostgreSQL)

This version of the Update statement uses a Join in the FROM clause. It’s similar to other statements like Select and allows you to retrieve the value from one table and use it as a value to update in another table.

Our example looks like this.

UPDATE p
SET p.account_number = a.account_number
FROM person p
INNER JOIN account a
ON p.person_id = a.person_id;

This query will update the account_number in the person table to the account_number in the account table. The data is matched on the person_id field as mentioned in the Join clause.

Notice that there is only one table in the UPDATE clause (the “p” table) and there are the two tables in the From clause.

 

2 – Update with From Second Table

Works with: PostgreSQL, SQL Server (not MySQL, Oracle)

This is a variation on the Update with Join method above that works with PostgreSQL.

UPDATE person
SET account_number = account.account_number
FROM account
WHERE person.person_id = account.person_id;

The difference is that the table to be updated is not mentioned in the FROM clause.

While we’re here, if you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here:

 

3 – Update with Join in Update Clause

Works with: MySQL (not PostgreSQL, SQL Server, Oracle)

This method is slightly different to the previous two. The join is performed in the Update clause, and not in the From clause.

UPDATE person
INNER JOIN account
ON person.person_id = account.person_id
SET person.account_number = account.account_number;

It seems to only work on MySQL.

 

4 – Update with Inline View

Works with: Oracle (not MySQL, SQL Server, PostgreSQL)

This method uses an inline view to ensure a single table is being updated.

UPDATE (
  SELECT
  p.person_id,
  p.account_number AS person_account_number,
  a.account_number
  FROM person p
  INNER JOIN account a ON p.person_id = a.person_id
) sub
SET sub.person_account_number = sub.account_number;

The Select query inside the Update statement looks up the two tables and constructs the right data set to use. Then the Set clause will set the person account number to the account number value.

If we run this in Oracle, we may get this error:

ORA-01779: cannot modify a column which maps to a non key-preserved table

This will happen depending on the tables and query that you have. Creating primary keys on the table may still result in this error.

So, you could try this method, but it may not work for you.

There are other approaches for Oracle databases though.

 

5 – Update with Subquery

Works with: Oracle, SQL Server, MySQL, PostgreSQL

Another way to update a table based on a Select query from another table is to use a subquery.

UPDATE person
SET account_number = (
  SELECT account_number
  FROM account
  WHERE account.person_id = person.person_id
);

This query will look up the right account number to use in the subquery, which joins to the outer person table and return it to the SET clause.

This is also the only approach to work with all four databases in this guide.

 

6 – Update using WITH Clause

Works with: SQL Server, PostgreSQL (not Oracle, MySQL)

You can use a subquery in the WITH clause (called a CTE or Common Table Expression) to update data in one table from another table.

WITH subquery AS (
  SELECT
  account_id,
  account_number,
  person_id
  FROM account
)
UPDATE person
SET account_number = subquery.account_number
FROM subquery
WHERE person.person_id = subquery.person_id;

This has the advantage of making the UPDATE part of the statement simple, and any complex logic for the query can go in the WITH clause.

 

7 – Merge Statement

Works with: Oracle, SQL Server (not MySQL, PostgreSQL)

Finally, the MERGE statement can be used to update data based on another table. It’s designed to “update if there is a match and insert if there is not”, but it can be tweaked to not insert a record.

MERGE INTO person p
USING account a
ON (p.person_id = a.person_id)
WHEN MATCHED THEN
  UPDATE SET p.account_number = a.account_number;

MySQL and PostgreSQL don’t support the MERGE statement, so it can’t be tweaked to fit those databases.

 

Summary

Here’s a summary of each method and which database they are compatible with.

No.	Method	Oracle	SQL Server	MySQL	PostgreSQL
1	Update with Join	No	Yes	No	No
2	Update with From Second Table	No	Yes	No	Yes
3	Update with Join in Update Clause	No	No	Yes	No
4	Update with Inline View	Yes	No	No	No
5	Update with Subquery	Yes	Yes	Yes	Yes
6	Update using WITH Clause	No	Yes	No	Yes
7	Merge Statement	Yes	Yes	No	No

Updating data in one table based on data in another table is something you may do occasionally. It’s possible in each database, but the methods are different.

If you want an easy-to-use PDF guide for the main features in different database vendors, get my SQL Cheat Sheets here: