这个和ORACLE的语法基本一至。
MySQL 没有这个东东，但是MySQL 提供了一个方便的语法：
测试如下（SQLServer 2008 里有 Date 这个字段类型，2005 里没有）：
CREATE TABLE CSK_Store_ProductViewTracking(
ID INT NOT NULL IDENTITY(1,1),
ProductID INT NOT NULL,
RetailerID INT NOT NULL,
[Date] DATE NOT NULL,
[COUNT] INT NOT NULL DEFAULT 0
CONSTRAINT CSK_Store_ProductViewTracking_PK PRIMARY KEY (ProductID, RetailerID, Date)
);
下面提供三个 MERGE INTO 的示例，但是在数据表里没有数据的情况下，只有最后一个可以插入，注意写法：
1,
MERGE INTO CSK_Store_ProductViewTracking AS Target
USING (SELECT * FROM CSK_Store_ProductViewTracking WHERE ProductID = 1 AND RetailerID = 2 AND [Date] = '2009-11-16') AS Source
ON (Target.ProductID = Source.ProductID AND Target.RetailerID = Source.RetailerID AND Target.Date = Source.Date)
WHEN MATCHED THEN
UPDATE SET Target.[COUNT] = Target.[COUNT] + 1
WHEN NOT MATCHED THEN
INSERT (ProductID, RetailerID, Date) VALUES( 1,2,'2009-11-16');
2,
MERGE INTO CSK_Store_ProductViewTracking AS Target
USING (SELECT * FROM CSK_Store_ProductViewTracking) AS Source
ON (Target.ProductID = 1 AND Target.RetailerID = 2 AND Target.Date = '2009-11-16')
WHEN MATCHED THEN
UPDATE SET Target.[COUNT] = Target.[COUNT] + 1
WHEN NOT MATCHED THEN
INSERT (ProductID, RetailerID, Date) VALUES( 1,2,'2009-11-16');
3,
MERGE INTO CSK_Store_ProductViewTracking AS Target
USING (SELECT 1, 2, '2009-11-16') AS Source ( ProductID, RetailerID, [Date] )
ON (Target.ProductID = Source.ProductID AND Target.RetailerID = Source.RetailerID AND Target.Date = Source.Date)
WHEN MATCHED THEN
UPDATE SET Target.[COUNT] = Target.[COUNT] + 1
WHEN NOT MATCHED THEN
INSERT (ProductID, RetailerID, Date) VALUES( 1,2,'2009-11-16');
MERGE INTO 的语法如：
| < Prev | Next > |
|---|