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”.
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.
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);
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.
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.
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.
Here’s a summary of each method and which database they are compatible with.
|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|
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.