[Comments] Only update rows that changed? Try using EXISTS and EXCEPT

[chadbaldwin]

https://chadbaldwin.net/2020/12/30/only-update-rows-that-changed.html

[EitanBlumin]

Super-cool method! This is my new favorite thing now 😁

[carthax12]

Brilliant! I only had to change one thing. My table is being updated from a datadump table. It can have one or more records for a given Report, and the only difference in each **otherwise-identical Report is the included Person data (Type of Person, FirstName, LastName, Address, etc...) So the original USING statement in your code returns multiple records in my datadump table, which throws an error when doing the UPDATE statement.

I changed it to this:

USING (select top 1 with ties * from #Updates u order by row_number() over (PARTITION BY ReportNumber ORDER BY ID DESC)) d on d.ReportNumber = u.ReportNumber

...which only returns one record per unique ReportNumber

**Seriously, the group of folks who designed the datadump I receive every day needs to be forced to listen to an out-of-tune piano playing a duet with an out-of-tune bagpipes while a tone-deaf singer sings "Ave Maria" for the rest of their lives.