The Hidden SQL Server Gem: UPDATE from SELECT [http://sqlity.net/en/2867/update-from-select/] (example script, ©sqlity.net 2015, all rights reserved)

The Hidden SQL Server Gem - UPDATE from SELECT.sql

/**
 *  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;
------------------------------------------