Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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