已知2张表,ProductNews和Product
ProductNews
ProductID ProductName Price
4100037 硬盘 50
4100038 鼠标 30
4100039 键盘 50
Product
ProductID ProductName Price
4100037 优盘 55
4100038 鼠标 30以下命令执行出错,请帮忙查看下
MERGE ProductNew AS d
USING
Product
AS s
ON s.ProductID = d.ProductId
and s.Price=d.Price
WHEN NOT MATCHED by target THEN
INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
when not matched by source then
update set d.Price = s.Price
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName;
ProductNews
ProductID ProductName Price
4100037 硬盘 50
4100038 鼠标 30
4100039 键盘 50
Product
ProductID ProductName Price
4100037 优盘 55
4100038 鼠标 30以下命令执行出错,请帮忙查看下
MERGE ProductNew AS d
USING
Product
AS s
ON s.ProductID = d.ProductId
and s.Price=d.Price
WHEN NOT MATCHED by target THEN
INSERT( ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
when not matched by source then
update set d.Price = s.Price
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName;
insert into productnews select '4100037','硬盘',50
insert into productnews select '4100038','鼠标',30
insert into productnews select '4100039','键盘',50
create table Product(ProductID varchar(10),ProductName nvarchar(10),Price int)
insert into Product select '4100037','优盘',55
insert into Product select '4100038','鼠标',30
go
MERGE ProductNews AS d
USING
Product AS s
ON s.ProductID = d.ProductId
and s.Price=d.Price
WHEN NOT MATCHED by target THEN
INSERT(ProductID,ProductName,Price)
VALUES(s.ProductID,s.ProductName,s.Price)
when not matched by source then
update set d.Price = Price
WHEN MATCHED THEN
UPDATE SET d.ProductName = s.ProductName;
select * from ProductNews
/*
ProductID ProductName Price
---------- ----------- -----------
4100037 硬盘 50
4100038 鼠标 30
4100039 键盘 50
4100037 优盘 55(4 行受影响)
*/
go
drop table productnews,Product