How to perform UPDATE from SELECT statement

Updating data

An UPDATE statement in SQL is to modify existing record(s) in a table. It allows us to change the values in one or more columns. 

-- update table
UPDATE table_name 
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

Note: We will be using the following two tables to demonstrate all the examples below.

Users table

ID

Username

Email

Birthdate

Amount_spent

1

'john_doe'

john@example.com

1990-05-15

225.80

2

'jane_smith'

jane@example.com

1998-10-22

341.25

3

'alex_brown'

alex@example.com

1995-02-08

0

4

'emily_watson'

emily@example.com

1992-07-30

0

5

'micheal_jackson'

micheal@example.com

1985-08-29

0

6

'susan_walker'

susan@example.com

NULL

0

7

'lisa_jones'

lisa@example.com

1998-03-17

0

Orders table

order_id

user_id

order_date

total_payment

101

1

2023-08-01

150.00

102

2

2023-08-02

220.50

103

1

2023-08-03

75.80

104

3

2023-08-04

350.00

105

4

2023-08-05

45.25

106

2

2023-08-06

120.75

107

2

2023-08-07

85.40

Let’s look at an example of an UPDATE query being used to modify a record in the users table. 

-- birthdate of username: susan_walker before
SELECT username, birthdate
FROM users
WHERE username = 'susan_walker';
UPDATE users
SET birthdate = '1969-12-15'
WHERE username = 'susan_walker';
-- birthdate of username: susan_walker after
SELECT username, birthdate
FROM users
WHERE username = 'susan_walker';

Here we are using a constant value (i.e. '1969-12-15') to change the birthdate of susan_walker.

What if we want to update a record using value(s) from another table in the database? This is where we would require an UPDATE from SELECT query. There are a couple of ways to do this.

SELECT subquery method

A subquery, also known as a nested query, is a query that is embedded into another query. Subqueries can be placed in different parts of the SQL statement depending on their purpose. For example, in the code below, we have used subquery in the SET clause.

Example

Another order was placed:

order_id

user_id

order_date

total_amount

108

7

2023-08-08

50.65

The amount_spent attribute in the users table represents the sum of the total_payment of all orders for each user. Let's update and add the total_amount of this order to the amount_spent for user with ID number 7 in the users table.

-- amount_spent by id number 7 before
SELECT id, amount_spent
FROM users
WHERE id = 7;
-- UPDATE from SELECT
UPDATE users
SET amount_spent = 
(SELECT SUM(total_payment) FROM orders WHERE user_id = 7)
WHERE id = 7;
-- amount_spent by id number 7 after
SELECT id, amount_spent
FROM users
WHERE id = 7;

There are also a few drawbacks to this method. The readability of the code will decrease as the complexity of the subquery increase, thus making it harder to keep track. Moreover, with large datasets, the execution time and space requirements increase significantly because the subquery is executed for each row being updated.

Join method

A Join is used to combine data from two or more tables based on a condition. Inner Join (which we will be using here) is a type of join that outputs only the rows that have matching values in both tables.

The tables are joined based on an attribute present in both tables. The attribute may have different names in both tables, but the values that it represents have to be the same.

In the code below, the joining condition we have used is the user identity number in both tables - id in users and user_id in orders.

Example

The amount_spent column for all the users is currently set to 0. We will use INNER JOIN to sum the total_amount from the orders table for each user and then update the amount_spent in the users table.

-- amount_spent user before
select username, amount_spent
FROM users;
-- UPDATE from SELECT
UPDATE users
INNER JOIN (
    SELECT user_id, SUM(total_payment) AS total_sum
    FROM orders
    GROUP BY user_id
) AS order_totals ON users.id = order_totals.user_id
SET users.amount_spent = order_totals.total_sum;
-- amount_spent users after
select username, amount_spent
FROM users;

A drawback of this method is its inability to handle unmatched instances between the two tables. Based on the join condition, if the match is not found, no update will occur for that row. This can result in inconsistency if the query was meant to update all the records regardless of a match.

MERGE method

Also known as an “upsert”, the MERGE statement is the combination of the UPDATE and INSERT commands. It modifies existing records in the table and adds records if they do not exist. Based on the query, the MERGE synchronizes the records in the target table using the source table.

Example

Note: Since MERGE statement is not available in MySQL, we will be using Microsoft SQL Server.

We will be performing the same operation as in the previous example.

MERGE INTO users AS u
USING (
    SELECT user_id, SUM(total_payment) AS total_sum
    FROM orders
    GROUP BY user_id
) AS order_totals ON u.id = order_totals.user_id
WHEN MATCHED THEN
    UPDATE SET u.amount_spent = order_totals.total_sum;
select * from users;

A drawback of the MERGE statement is that it is not supported on the majority of database systems available, thus limiting the choice of the user. Furthermore, as the size of the source and target tables increase, so does the time taken for its execution.

Conclusion

The UPDATE from SELECT statement enables data modifications in an SQL database table based on information retrieved from another table using SELECT. The following three methods were discussed to achieve this:

  • The first one uses an UPDATE statement with a SELECT subquery, which allows modifying data based on values from other tables.

  • The second approach involves an INNER JOIN to combine data for updates, offering better performance but reducing readability.

  • The third and last method combines UPDATE and INSERT, which provides a comprehensive way to synchronize data. However, it it essential to take into account its complexity and lack of support of the majority of database platforms.

Selecting the appropriate method to implement UPDATE from SELECT depends on the specific scenario's complexity, readability, and performance considerations.

Free Resources

Copyright ©2026 Educative, Inc. All rights reserved