如何在合并的插入和更新中使用第三个表中的值

Question

我的merge语句如下:

MERGE TargetProducts AS Target
USING SourceProducts    AS Source
ON Source.ProductID = Target.ProductID
WHEN NOT MATCHED BY Target THEN
INSERT (ProductID,ProductName, Price, Testvalue) 
VALUES (Source.ProductID,Source.ProductName, Source.Price, (select value from testtable where id=source.ProductID)
WHEN MATCHED THEN UPDATE SET
Target.ProductName  = Source.ProductName,
Target.Price        = Source.Price
Target.testvalue=(Select value from testtable where id=source.ProductID)

是否有办法做到以上。也就是说，使用另一个表中的值。

Answer 1

您还可以指定一个查询作为源，而不仅仅是一个表

例如:

MERGE TargetProducts AS Target
USING ( 
SELECT   s.ProductID,s.ProductName, s.Price  , t.Testvalue      
FROM SourceProducts s
JOIN testtable t
ON t.id = s.ProductId 
)AS Source
ON Source.ProductID = Target.ProductID
WHEN NOT MATCHED BY Target THEN
INSERT (ProductID,ProductName, Price,Testvalue) 
VALUES (Source.ProductID,Source.ProductName, Source.Price, Source.Testvalue)
WHEN MATCHED THEN UPDATE SET
Target.ProductName  = Source.ProductName,
Target.Price        = Source.Price,
Target.testvalue    = Source.Testvalue;