CODEWITHSUNDEEP
sql-servert-sql
EngineerSpock
EngineerSpock

Reputation: 2675

Return table of changed rows

In Postgres, I could write a CTE like in the following example:

WITH ProdUpdate AS (
    UPDATE Products
    SET Discontinued = 1
    WHERE UnitsInStock < 10
    RETURNING ProductID
)
SELECT * INTO DiscontinuedOrders
FROM OrderDetails
WHERE ProductID IN (SELECT ProductID FROM ProdUpdate);

How can I re-implement that in T-SQL?

Upvotes: 0

Views: 41

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8819

On the UPDATE statement you're probably looking for the OUTPUT Clause (Transact-SQL), but on SQL Server you can't nest UPDATE statements inside CTEs or JOINs like you're trying to do.

On SQL Server the code would be slightly restructured like the following...

-- Schema setup...

drop table if exists dbo.Products;
drop table if exists dbo.OrderDetails;
drop table if exists dbo.DiscontinuedOrders;

create table dbo.Products (
  ProductID int not null identity(1, 1),
  UnitsInStock int not null,
  Discontinued bit not null
);

create table dbo.OrderDetails (
  OrderID int not null,
  ProductID int not null,
  Quantity int not null
);

insert dbo.Products (UnitsInStock, Discontinued) values
  (10, 0),
  (9, 0);

insert dbo.OrderDetails (OrderID, ProductID, Quantity) values
  (1, 1, 1),
  (2, 2, 2);

-- Update/insert code...

declare @ProdUpdate table (
  ProductID int not null
);

update dbo.Products
set Discontinued = 1
output inserted.ProductID into @ProdUpdate
where UnitsInStock < 10;

select OD.*
into dbo.DiscontinuedOrders
from dbo.OrderDetails OD
join @ProdUpdate PU on OD.ProductID = PU.ProductID;

go

select *
from dbo.DiscontinuedOrders;

Which outputs...

OrderID ProductID Quantity
2 2 2

Upvotes: 1

Related Questions