Question

我正试图把头放在MERGE sql 语句上。我要做到的是:

在 CScolorOrders 表格中插入新值,但如果已经存在有 colID 和 ordID 的记录,则更新 corQTimes 栏。

这就是我最后的结局:

MERGE INTO CSScolorOrders AS TARGET 
USING (SELECT * FROM CSScolorOrders WHERE ordID = 3) AS SOURCE 
ON (SOURCE.colID = 1) WHEN 
MATCHED THEN UPDATE SET corQuantity = 1 
WHEN
  NOT MATCHED BY TARGET
  THEN INSERT (colID, ordID, corQuantity) VALUES (1, 3, 1);

不幸的是,它没有提出任何例外,所以我不知道为什么它不起作用。

Answer 1

正如讨论到的""http://www.bidn.com/blogs/bretupdegraff/bidn-blog/239/using-the-new-tsql-merge-state- with-sql-server-2008" rel=“ noreferr”-sql-server-2008"这里,你会看到一个合并完全听上去一样。如果 X 匹配, 则取两个表格, 并搜索您加入它们的价值。如果 X 匹配的话, 您会在记录上进行更新。如果不存在的话, 您将会使用所选的值在目标表格上进行插入。

以你的情况我不完全确定你是否加入

  (  ON (SOURCE.colID = 1)   )

正确。我非常肯定这需要

on(Source.colID = Target.colID)

因此,完整发言应该是:

MERGE INTO CSScolorOrders AS TARGET 
USING (SELECT * FROM CSScolorOrders WHERE ordID = 3) AS SOURCE 
on(Source.colID = Target.colID)
WHEN MATCHED THEN 
  UPDATE SET corQuantity = 1 
WHEN NOT MATCHED BY TARGET
  THEN INSERT (colID, ordID, corQuantity) VALUES (1, 3, 1);

但我没有测试过这个, 也没有100%确定您的表格列是什么, 以及您正试图加入什么。但我提供的链接应该将您指向正确的方向。

希望这有帮助!

Answer 2

MERGE INTO accounting_values AS Target
USING (select @entity_id as entity_id, @fiscal_year as fiscal_year, @fiscal_period as       fiscal_period) AS source
ON (target.entity_id = source.entity_id  AND target.fiscal_year = source.fiscal_year AND target.fiscal_period = source.fiscal_period)
WHEN MATCHED THEN
    UPDATE SET value = @value
WHEN NOT MATCHED BY Target THEN
    INSERT 
(entity_id, fiscal_year, fiscal_period, value) 
    VALUES (@entity_ID, @fiscal_year, @fiscal_period, @value);

这是经过测试的。它不参考表格两次。我试图复制 MySQL 在 DUPUCT KEY UPDATE 上。

Answer 3

MERGE CSScolorOrders AS TARGET 
USING (SELECT * FROM CSScolorOrders WHERE ordID = 3) AS SOURCE 
ON (SOURCE.colID = TARGET.colID) WHEN 
MATCHED THEN UPDATE SET corQuantity = 1 
WHEN
  NOT MATCHED
  THEN INSERT (colID, ordID, corQuantity) VALUES (1, 3, 1);

Answer 4

在表格中更新或INSTERT, 不论该表格是否包含记录, 您可以按以下方式运行:

ensure USING clause returns ONE entry
express the matching condition(s) in the ON clause

以你们为例(下文以甲骨文为例试验):

MERGE INTO CSScolorOrders AS TARGET 
USING (SELECT  OneEntry  FROM DUAL) AS SOURCE
ON (colID = 1 and ordID = 3) WHEN 
MATCHED THEN UPDATE SET corQuantity = 1 
WHEN
  NOT MATCHED BY TARGET
  THEN INSERT (colID, ordID, corQuantity) VALUES (1, 3, 1);

大多数数据库都有一个DUMMY表格,允许任意 SELECT(上面,我使用DUAL),但如果我们只使用您问题中提到的表格,您可以替换

SELECT  OneEntry  FROM DUAL

与

SELECT COUNT(*) FROM CSScolorOrders

友情链接