我正在尝试编写一个查询来检查仪表是否存在,然后进行更新,它不会插入到每月数据表中!问题是我对语法感到困惑,也不知道该怎么做!
这是数据库的设计。假设我有计时器号码2012345,我该怎么做?谢谢

发布于 2013-07-02 04:31:22
在SQL Server中(仅使用name作为演示,您当然需要更多字段)
MERGE meters AS target
USING (SELECT '2012345') AS source (meternumber)
ON (target.meternumber = source.meternumber)
WHEN MATCHED THEN
UPDATE SET name='MeterUpdate#1', meternumber=source.meternumber
WHEN NOT MATCHED THEN
INSERT (name, meternumber) VALUES ('MeterInsert#1', source.meternumber);在MySQL中,在meters(Meternumber)上创建唯一索引;
CREATE UNIQUE INDEX bop ON meters(meternumber);然后使用插入/更新;
INSERT INTO meters (name, meternumber) VALUES ('MeterInsert#1', '2012345')
ON DUPLICATE KEY UPDATE name='MeterUpdate#1', meternumber='2012345';发布于 2013-07-02 02:43:28
SQL Server具有可用作UPSERT的合并http://msdn.microsoft.com/en-us/library/bb522522(v=sql.105).aspx。下面是从文档中复制的示例(示例A):
MERGE dbo.FactBuyingHabits AS Target
USING (SELECT CustomerID, ProductID, PurchaseDate FROM dbo.Purchases) AS Source
ON (Target.ProductID = Source.ProductID AND Target.CustomerID = Source.CustomerID)
WHEN MATCHED THEN
UPDATE SET Target.LastPurchaseDate = Source.PurchaseDate
WHEN NOT MATCHED BY TARGET THEN
INSERT (CustomerID, ProductID, LastPurchaseDate)
VALUES (Source.CustomerID, Source.ProductID, Source.PurchaseDate)
OUTPUT $action, Inserted.*, Deleted.*; https://stackoverflow.com/questions/17411108
复制相似问题