user3411176 user3411176 - 1 month ago 7
SQL Question

MS SQL - Conditional UPDATE return result

I have a stored procedure which is doing the following.

The populated target table data is checked against several similar source tables for a match (based on name and address data). If a match is found in the first table then it updates the target with a flag identifying which source table the match was from. However if it doesn't find a match I need it to look in the next source table and the next until either a match is found or not as the case may be.

Is there an easy way for the UPDATE statement to provide some kind of return value I can query to say whether it updated the target table? I would like to use this return value so that I can skip checking subsequent source tables unnecessarily.

Otherwise will I have to perform the conditional UPDATE then do a separate query to determine if the UPDATE actually updated the flag?

Answer

I like the soulution of Gordon, but I do not think you actualy need it.

Simply run the updates in order:

UPDATE BASE_TABLE
SET FLAG='first_table'
where FLAG IS null AND
EXIST (SELECT 1 FROM first_table f1 where f1.ID = ID)

UPDATE BASE_TABLE
SET FLAG='second_table'
where FLAG IS null AND
EXIST (SELECT 1 FROM second_table f2 where f2.ID = ID)

... And so on. You dont need to check every row conditionaly, that would be very slow.

Comments