如何获得从联接插入值派生的列？
-2
对于以下示例
CREATE TABLE [dbo].[Customer] (CustomerID int primary key, ShipMethodRef INT)
INSERT INTO [dbo].[Customer] VALUES (5497, 20);
CREATE TABLE [dbo].ShipMethod(ShipMethodID int PRIMARY KEY, Name varchar(10));
INSERT INTO [dbo].ShipMethod VALUES (20, 'Fedex'), (21, 'UPS')
UPDATE [dbo].[Customer]
set ShipMethodRef = CASE WHEN EXISTS (SELECT ShipMethodID from [dbo].[ShipMethod]
WHERE [dbo].[ShipMethod].Name = 'UPS')
THEN (SELECT ShipMethodID from [dbo].[ShipMethod]
WHERE [dbo].[ShipMethod].Name = 'UPS')
ELSE curTable.ShipMethodRef END
OUTPUT ShipMethod.Name as ShipMethodName
FROM [dbo].[Customer] curTable
JOIN [dbo].ShipMethod ShipMethod ON curTable.ShipMethodRef = ShipMethod.ShipMethodID
WHERE CustomerID=5497;
该OUTPUT子句返回Fedex-如何更改它以反映客户的运输方式为UPS（因为其运输方式ID现在为21）的插入后状态？
展开
收起
1
条回答
写回答
写回答
-
我不认为只能用一条语句来完成此操作，除非马丁在其注释中显示出来，但是您可以将输出插入表变量或临时表中，然后从连接到转换表的表中进行选择。 这是我的操作方式（请注意，更新语句已简化）：
DECLARE @UpdatedIds AS TABLE (ShipMethodID int); UPDATE [dbo].[Customer] SET ShipMethodRef = COALESCE(( SELECT ShipMethodID FROM [dbo].[ShipMethod] WHERE [dbo].[ShipMethod].Name = 'UPS' ), ShipMethodRef) OUTPUT inserted.ShipMethodRef INTO @UpdatedIds FROM [dbo].[Customer] WHERE CustomerID=5497; SELECT SM.ShipMethodID, SM.Name FROM [dbo].ShipMethod AS SM JOIN @UpdatedIds AS Updated ON SM.ShipMethodID = Updated.ShipMethodID2020-01-04 15:34:30赞同 展开评论 打赏
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
相关问答
