CODEWITHSUNDEEP
sqldatabaseimport
LosDichtos
LosDichtos

Reputation: 15

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:

TempTable

tblContacts (Main table)

Upvotes: 0

Views: 4495

Answers (2)

Rayman
Rayman

Reputation: 61

You could combine all columns into a single string of text, and then perform an MD5 hash on them to detect any differences between all columns simultaneously. This negates the use the OR logic which is exceedingly slow. This is far better for On Prem databases than Cloud-based deployments, but it is fast and simple.

UPDATE C SET 
    C.FirstName = T.FirstName,
    C.LastName = T.Lastname
FROM tblContact C
JOIN #TestTable T
    ON T.PersNbr = C.PersNbr
WHERE 
    HASHBYTES('MD5',C.FirstName + C.LastName + C.Title) 
    != HASHBYTES('MD5',T.FirstName + T.LastName + T.Title)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 2

Related Questions