I have a table, tblstars_new, which is updated weekly from the client. I have another table, tblstars, which needs to import new and updated data from tblstars_new.
Finding rows in tblstars_new that do not exist in tblstars, and then adding then to tblstars is simple.
But, I also need to find rows in tblstars_new in which the column PandA_Code has changed, and then update the identical row in tblstars.
This query tells me which rows from tblstars_new have PandA_Code's that have changed and need to be updated in tblstars.
JOIN tblstars s ON sn.Student_ID_Number = s.Student_ID_Number AND sn.PandA_Code != s.PandA_Code
MERGE tblstars AS T -- Target
USING tblstars_new AS S -- Source
(T.Student_ID_Number = S.Student_ID_Number AND T.PandA_Code != S.PandA_Code)
WHEN NOT MATCHED BY TARGET
UPDATE SET T.PandA_Code = S.PandA_Code
This is simply done by using join instead of merge statement:
UPDATE T SET T.PandA_Code = S.PandA_Code FROM tblstart T INNER JOIN tblstars_new P ON P.Student_ID_Number = T.Student_ID_Number WHERE T.PandA_Code <> S.PandA_Code