Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
datameer banner faq

How to UPDATE a table with a SELECT statement in SQL Server?

  • How-Tos FAQs
  • December 16, 2018

Let’s create two tables as below to demonstrate UPDATE from SELECT.

CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    order_date DATETIME,
    total_orders INT
);
INSERT INTO  orders
SELECT 1,'Jack', '2020-02-03', 4 UNION ALL
SELECT 2, 'Rose', '2020-01-09', 19 UNION ALL
SELECT 3,'Jack', '2020-02-03', 40 UNION ALL
SELECT 4, 'Rose', '2020-01-09', 21 ;


CREATE TABLE order_details (
    order_detail_id INT,
    order_id VARCHAR(100),
    item VARCHAR(100)
);

INSERT INTO  order_details
SELECT 1,1, 'laptop' UNION ALL
SELECT 2,2, 'mouse' UNION ALL
SELECT 3,3, 'headphone' UNION ALL
SELECT 4,4, 'pendrive'

In SQL Server, we can UPDATE from a SELECT in two different ways:

1.      USING UPDATE

UPDATE can be made either using one table or by joining multiple tables. But we can only update only one table from all the joined tables. We cannot update multiple tables at once, even if we can have multiple tables in the joins.

UPDATE od
SET item = 'Mac'
FROM orders o
INNER JOIN order_details od
    ON o.order_id = od.order_id
WHERE o.order_id = 1

A Faster Way To Build Data Models

Mix SQL and no code to create data models in a few clicks vs months.

Try Datameer Today

2.     USING MERGE

In SQL Server 2008 and newer versions, we can use the MERGE Statement to UPDATE. MERGE can be used not only to UPDATE but also for the insert and the deletion of data all in a statement.

MERGE INTO orders o
  USING order_details od
  ON o.order_id = od.order_id
    AND od.item = 'mouse'
WHEN MATCHED THEN
  UPDATE
  SET order_date = '2019-12-09'
          ,total_orders = 10;

Up Next:

Read Can we use stored procedures in a select statement in SQL server?

More Resources We Think You Might Like

SQL FAQ Feat

How to select the first row of each GROUP BY in...

Let us create a table ‘orders’ to replicate the problem. CREATE TABLE orders (       ...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to return all information of a row with MAX...

Suppose we have a table “score” as below, and we need all information on a row with the maximum s...

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

How to INSERT values in a table using SELECT qu...

This can be done in below: INSERT INTO orders (ID, customer_name, order_date, total_orders) SELEC...

  • How-Tos FAQs
  • December 16, 2018