关于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;
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
相关阅读 更多
热门阅读
-
office 2019专业增强版最新2021版激活秘钥/序列号/激活码推荐 附激活工具
阅读:74
-
如何安装mysql8.0
阅读:31
-
Word快速设置标题样式步骤详解
阅读:28
-
20+道必知必会的Vue面试题(附答案解析)
阅读:37
-
HTML如何制作表单
阅读:22
-
百词斩可以改天数吗?当然可以,4个步骤轻松修改天数!
阅读:31
-
ET文件格式和XLS格式文件之间如何转化?
阅读:24
-
react和vue的区别及优缺点是什么
阅读:121
-
支付宝人脸识别如何关闭?
阅读:21
-
腾讯微云怎么修改照片或视频备份路径?
阅读:28