+ -
当前位置:首页 → 问答吧 → 关于sql中Merge语句问题

关于sql中Merge语句问题

时间:2011-12-07

来源:互联网

已知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;

作者: yufenfei2011   发布时间: 2011-12-07

SQL code
create table ProductNews(ProductID varchar(10),ProductName nvarchar(10),Price int)
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

作者: qianjin036a   发布时间: 2011-12-08