sql - 一个 t-sql 查询中 UPDATE OUTPUT 内的 INNER JOIN

Question

需要将 UPDATE OUTPUT 返回的结果集与另一个表进行 INNER JOIN 并返回结果。可能吗？

这是一个小例子:

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

需要更新 CustomerOrders 表上的 OrderId 并返回客户姓名，全部一次性完成。到目前为止我只能返回CustomerIds:

UPDATE CustomerOrders SET OrderId=NULL 
OUTPUT Deleted.CustomerId
WHERE OrderId='1'

Answer 1

TSQL/SQL Server 2005+ 支持 UPDATE 子句中的 JOIN - 请参阅 documentation :

UPDATE CUSTOMERORDERS
   SET orderid = NULL
OUTPUT c.customername
  FROM CUSTOMERORDERS co
  JOIN CUSTOMERS c ON c.customerid = co.customerid