Instantly share code, notes, and snippets.
sqlity/The Hidden SQL Server Gem - UPDATE from SELECT.sql
Last active Mar 16, 2019
The Hidden SQL Server Gem: UPDATE from SELECT [http://sqlity.net/en/2867/update-from-select/] (example script, ©sqlity.net 2015, all rights reserved)
/** | |
* Example script for blog post | |
* | |
* The Hidden SQL Server Gem: UPDATE from SELECT | |
* | |
* http://sqlity.net/en/2867/update-from-select/ | |
* | |
* ©sqlity.net 2015, all rights reserved | |
* | |
**/ | |
GO | |
IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; | |
IF OBJECT_ID('dbo.Customers') IS NOT NULL DROP TABLE dbo.Customers; | |
GO | |
CREATE TABLE dbo.Customers( | |
customer_id INT IDENTITY(1,1) CONSTRAINT [PK:dbo.Customers] PRIMARY KEY CLUSTERED, | |
name VARCHAR(100), | |
order_count INT | |
); | |
GO | |
INSERT INTO dbo.Customers(name) | |
OUTPUT INSERTED.* | |
VALUES('John Doe'),('Jane Doe'),('Joe Smith'); | |
GO | |
CREATE TABLE dbo.Orders( | |
order_id INT IDENTITY(1,1) CONSTRAINT [PK:dbo.Orders] PRIMARY KEY CLUSTERED, | |
customer_id INT CONSTRAINT [FK:dbo.Orders -> dbo.Customers] REFERENCES dbo.Customers(customer_id), | |
order_date DATETIME2 NOT NULL CONSTRAINT [DF:dbo.Orders(order_date)] DEFAULT SYSDATETIME(), | |
is_paid BIT NOT NULL CONSTRAINT [DF:dbo.Orders(is_paid)] DEFAULT 0, | |
is_shipped BIT NOT NULL CONSTRAINT [DF:dbo.Orders(is_shipped)] DEFAULT 0, | |
is_archived BIT NOT NULL CONSTRAINT [DF:dbo.Orders(is_archived)] DEFAULT 0 | |
); | |
GO | |
INSERT INTO dbo.Orders(customer_id,order_date,is_paid,is_shipped) | |
OUTPUT INSERTED.* | |
VALUES(1,'2015-03-14T12:23:09',1,1), | |
(1,'2015-04-14T12:34:19',1,0), | |
(2,'2015-04-04T07:23:04',0,1), | |
(3,'2015-08-04T09:51:24',1,1); | |
GO | |
SELECT * | |
FROM dbo.Orders AS O | |
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME()) | |
AND O.is_paid = 1 | |
AND O.is_shipped = 1; | |
GO | |
--UPDATE O SET /* | |
SELECT *, -- */ | |
is_archived = 1 | |
FROM dbo.Orders AS O | |
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME()) | |
AND O.is_paid = 1 | |
AND O.is_shipped = 1; | |
GO | |
UPDATE O SET /* | |
SELECT *, -- */ | |
is_archived = 1 | |
FROM dbo.Orders AS O | |
WHERE O.order_date < DATEADD(DAY,-90,SYSDATETIME()) | |
AND O.is_paid = 1 | |
AND O.is_shipped = 1; | |
GO | |
SELECT * | |
FROM dbo.Orders AS O; | |
GO | |
----------------------------------------------------------------------------- | |
----------------------------------------------------------------------------- | |
GO | |
--UPDATE C SET /* | |
SELECT *, -- */ | |
order_count = OA.cnt | |
FROM dbo.Customers AS C | |
JOIN( | |
SELECT O.customer_id, | |
COUNT(1) cnt | |
FROM dbo.Orders AS O | |
GROUP BY O.customer_id | |
)OA | |
ON C.customer_id = OA.customer_id; | |
GO | |
UPDATE C SET /* | |
SELECT *, -- */ | |
order_count = OA.cnt | |
FROM dbo.Customers AS C | |
JOIN( | |
SELECT O.customer_id, | |
COUNT(1) cnt | |
FROM dbo.Orders AS O | |
GROUP BY O.customer_id | |
)OA | |
ON C.customer_id = OA.customer_id; | |
GO | |
SELECT * FROM dbo.Customers AS C; | |
GO | |
----------------------------------------------------------------------------- | |
----------------------------------------------------------------------------- | |
GO | |
WITH order_counts AS | |
( | |
SELECT O.customer_id, | |
COUNT(1) cnt | |
FROM dbo.Orders AS O | |
GROUP BY O.customer_id | |
), | |
customer_order_counts AS | |
( | |
SELECT | |
C.customer_id, | |
C.name, | |
C.order_count, | |
OC.cnt new_order_cnt | |
FROM dbo.Customers AS C | |
JOIN order_counts AS OC | |
ON C.customer_id = OC.customer_id | |
) | |
UPDATE COC SET /* | |
SELECT *, -- */ | |
order_count = COC.new_order_cnt | |
FROM customer_order_counts AS COC; | |
GO | |
----------------------------------------------------------------------------- | |
----------------------------------------------------------------------------- | |
GO | |
------------------------------------------ | |
-- Don't use this: | |
------------------------------------------ | |
-- UPDATE C SET /* | |
-- SELECT *, -- */ | |
-- last_order_date = O.order_date | |
-- FROM dbo.Customers AS C | |
-- JOIN dbo.Orders AS O | |
-- ON C.customer_id = O.customer_id; | |
------------------------------------------ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment