INNER JOIN within UPDATE OUTPUT in one t-sql query

Question

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' 

Answer 1

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