用于更新数据的SQL MERGE语句

I've got a table with data named energydata

我有一个名为energydata的数据表

it has just three columns

它只有三列

(webmeterID, DateTime, kWh)

I have a new set of updated data in a table temp_energydata.

我在表temp_energydata中有一组新的更新数据。

The DateTime and the webmeterID stay the same. But the kWh values need updating from temp_energydata table.

DateTime和webmeterID保持不变。但是kWh值需要从temp_energydata表更新。

How do I write the T-SQL for this the correct way?

如何以正确的方式编写T-SQL？

6 个解决方案

#1

104

Assuming you want an actual SQL Server MERGE statement:

假设您需要一个实际的SQL Server MERGE语句：

MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
USING dbo.temp_energydata AS source
    ON target.webmeterID = source.webmeterID
    AND target.DateTime = source.DateTime
WHEN MATCHED THEN 
    UPDATE SET target.kWh = source.kWh
WHEN NOT MATCHED BY TARGET THEN
    INSERT (webmeterID, DateTime, kWh)
    VALUES (source.webmeterID, source.DateTime, source.kWh);

If you also want to delete records in the target that aren't in the source:

如果您还想删除目标中不在源中的记录：

MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
USING dbo.temp_energydata AS source
    ON target.webmeterID = source.webmeterID
    AND target.DateTime = source.DateTime
WHEN MATCHED THEN 
    UPDATE SET target.kWh = source.kWh
WHEN NOT MATCHED BY TARGET THEN
    INSERT (webmeterID, DateTime, kWh)
    VALUES (source.webmeterID, source.DateTime, source.kWh)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Because this has become a bit more popular, I feel like I should expand this answer a bit with some caveats to be aware of.

因为这已经变得更受欢迎，我觉得我应该稍微扩展这个答案，并注意一些注意事项。

First, there are several blogs which report concurrency issues with the MERGE statement in older versions of SQL Server. I do not know if this issue has ever been addressed in later editions. Either way, this can largely be worked around by specifying the HOLDLOCK or SERIALIZABLE lock hint:

首先，有几个博客报告旧版SQL Server中MERGE语句的并发问题。我不知道这个问题是否曾在以后的版本中得到解决。无论哪种方式，这可以在很大程度上通过指定HOLDLOCK或SERIALIZABLE锁提示来解决：

MERGE INTO dbo.energydata WITH (HOLDLOCK) AS target
[...]

You can also accomplish the same thing with more restrictive transaction isolation levels.

您还可以使用更具限制性的事务隔离级别来完成相同的操作。

There are several other known issues with MERGE. (Note that since Microsoft nuked Connect and didn't link issues in the old system to issues in the new system, these older issues are hard to track down. Thanks, Microsoft!) From what I can tell, most of them are not common problems or can be worked around with the same locking hints as above, but I haven't tested them.

MERGE还有其他几个已知问题。（请注意，由于微软认为Connect并没有将旧系统中的问题与新系统中的问题联系起来，因此很难追查这些旧问题。谢谢，微软！）从我所知道的，大多数都不常见问题或可以使用与上面相同的锁定提示解决，但我没有测试它们。

As it is, even though I've never had any problems with the MERGE statement myself, I always use the WITH (HOLDLOCK) hint now, and I prefer to use the statement only in the most straightforward of cases.

事实上，即使我自己从未对MERGE语句有任何问题，我现在总是使用WITH（HOLDLOCK）提示，而我更喜欢仅在最简单的情况下使用该语句。

#2

I often used Bacon Bits great answer as I just can not memorize the syntax.

我经常使用培根比特很好的答案，因为我无法记住语法。

But I usually add a CTE as an addition to make the DELETE part more useful because very often you will want to apply the merge only to a part of the target table.

但我通常添加一个CTE作为一个补充，使DELETE部分更有用，因为通常你只想将合并应用于目标表的一部分。

WITH target as (
    SELECT * FROM dbo.energydate WHERE DateTime > GETDATE()
)
MERGE INTO target WITH (HOLDLOCK)
USING dbo.temp_energydata AS source
    ON target.webmeterID = source.webmeterID
    AND target.DateTime = source.DateTime
WHEN MATCHED THEN 
    UPDATE SET target.kWh = source.kWh
WHEN NOT MATCHED BY TARGET THEN
    INSERT (webmeterID, DateTime, kWh)
    VALUES (source.webmeterID, source.DateTime, source.kWh)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE

#3

If you need just update your records in energydata based on data in temp_energydata, assuming that temp_enerydata doesn't contain any new records, then try this:

如果你需要根据temp_energydata中的数据更新energydata中的记录，假设temp_enerydata不包含任何新记录，那么试试这个：

UPDATE e SET e.kWh = t.kWh
  FROM energydata e INNER JOIN 
       temp_energydata t ON e.webmeterID = t.webmeterID AND 
                            e.DateTime = t.DateTime

Here is working sqlfiddle

这是工作sqlfiddle

But if temp_energydata contains new records and you need to insert it to energydata preferably with one statement then you should definitely go with the answer that Bacon Bits gave.

但是如果temp_energydata包含新的记录，你需要将它插入到energydata中，最好只有一个声明，那么你一定应该选择Bacon Bits给出的答案。

#4

UPDATE ed
SET ed.kWh = ted.kWh
FROM energydata ed
INNER JOIN temp_energydata ted ON ted.webmeterID = ed.webmeterID

#5

Update energydata set energydata.kWh = temp.kWh 
where energydata.webmeterID = (select webmeterID from temp_energydata as temp)

#6

-6

THE CORRECT WAY IS :

正确的方法是：

UPDATE test1
INNER JOIN test2 ON (test1.id = test2.id)
SET test1.data = test2.data

#1

104