Update table with temp table only when values are different in the same columns

I want to update a table with contact data with data of a temporary table. Because there are a big amount of rows i have to update i just want the columns to be updated that have different values than the columns in the temporary table. (In the main table should only be the values be updated for the columns that also exist in the temporary table) The columns of the both tables can be assigned to each other with a personID.

I tried to update each column individually like this:

UPDATE tblContact 
SET tblContact.Lastname = t.Lastname
FROM tblContact c
INNER JOIN #TestTable t
ON c.PersNbr = t.PersNbr
WHERE c.Lastname != t.Lastname

To do this for every column isnt very performant. So do u know any possibility to check in an update statement if the value of two columns are different and only if the columns are different update the column in my main table? Or maybe a way to combine all my update statements for every column like the one aboth into one statement?

I am using Microsoft SQL Server as RDBMS. Following are diagrams of my two tables:

Answer

You can just set the columns:

UPDATE c 
    SET c.Lastname = t.Lastname,
        c.Firstname = t.Firstname,
        . . . 
FROM tblContact c INNER JOIN
     #TestTable t
     ON c.PersNbr = t.PersNbr
WHERE c.Lastname <> t.Lastname OR
      c.Firstname <> t.Firstname OR
      . . .;

If you expect a change in at least one column, then you don’t really need the WHERE clause. However, it is more efficient in SQL Server if you avoid unnecessary updates where no values change.

Advertisement

Answer