Evaluate each field from source to target and update

Question

I am using Microsoft Sql server 2014

I have a request where i have to check if ID exists ,If ID exists then update. However client wants to evaluate each field from source and check if there are any changes in target, if there are any changes update only that field and rest of the fields should be original.

So if my original request is, Source :

ID name company salary
1 dave ABC 50000$
2 ashley XXX 50000$

Target:

ID name company salary
1 dave ABC 50000$
2 ashley XXX 50000$

Updated request: Source :

ID name company salary
1 dave ABC 80000$
2 ashley XXX 80000$

Now I have to check each column to see if there are any changes , only if any column has any change then update only that field.

target:

ID name company salary
1 dave ABC 80000$
2 ashley XXX 80000$

I cannot use hashkey/flag field as my target table does not have these fields and I cannot alter them. What is the best way to do this? Any help is appreciated

Answer 1

You can build a dinamic update query server side testing the value or you can use an update with inner join and case statement If you use mysql you can try this way

update target t
inner join source s on t.id = s.id
set case when t.name <> s.name then s.name else t.name end,
    case when t.company <> s.company then s.company else t.company end,
    case when t.salary <> s.salary then s.salary else t.salary end