SQL MERGE statement to update data

Question

I've got a table with data named energydata

it has just three columns

(webmeterID, DateTime, kWh)

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

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

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

Answer 1

Assuming you want an actual SQL Server MERGE statement:

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:

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.

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.

Answer 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.

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

Answer 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:

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

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.

Answer 4

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

Answer 5

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

Answer 6

THE CORRECT WAY IS :

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