INNER JOIN within UPDATE OUTPUT in one t-sql query
13,972
TSQL/SQL Server 2005+ supports JOINs in the UPDATE clause - see the documentation:
UPDATE CUSTOMERORDERS
SET orderid = NULL
OUTPUT c.customername
FROM CUSTOMERORDERS co
JOIN CUSTOMERS c ON c.customerid = co.customerid
Author by
kateroh
Updated on June 06, 2022Comments
-
kateroh about 2 years
Need to INNER JOIN a resultset returned by UPDATE OUTPUT with another table and return results. Is it possible?
Here is a small example:
CREATE TABLE [dbo].[Customers] ( [CustomerId] [int], [CustomerName] [nvarchar](50) ) GO CREATE TABLE [dbo].[Orders] ( [OrderId] [int], [OrderName] [nvarchar](50) ) GO CREATE TABLE [dbo].[CustomerOrders] ( [CustomerId] [int], [OrderId] [int] ) GO INSERT INTO CustomerOrders (CustomerId, OrderId) VALUES (1, 1) INSERT INTO CustomerOrders (CustomerId, OrderId) VALUES (1, 2) INSERT INTO CustomerOrders (CustomerId, OrderId) VALUES (2, 1) GO
Need to update OrderId on CustomerOrders table and return names of the customers, all in 1 shot. So far I can only return CustomerIds:
UPDATE CustomerOrders SET OrderId=NULL OUTPUT Deleted.CustomerId WHERE OrderId='1'