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?
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.