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 = 12. 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?
