MS SQL - Conditional UPDATE return result

Question

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?

You dont need any conditional, simply run the updates after the another. If it found an ID, it will update the table else nothing will happen. — PeterRing
– PeterRing, Commented Oct 24, 2016 at 11:42

Gordon Linoff · Accepted Answer · 2016-10-24 11:42:52Z

2

Probably the safest approach is to use the OUTPUT clause. This will return the modified rows into a new table.

You can check the table to see if any rows have been updated.

One advantage of the OUTPUT clause is that you can update multiple rows at the same time.

answered Oct 24, 2016 at 11:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neo Over a year ago

beat me to it by a second!

user3411176 Over a year ago

Crikey! thank you very much for that incredibly prompt response. I'll read up from the the link you've provided.

PeterRing · Accepted Answer · 2016-10-24 12:03:08Z

0

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.

answered Oct 24, 2016 at 12:03

PeterRing

1,81713 silver badges21 bronze badges

3 Comments

user3411176 Over a year ago

Hi Peter, I wasn't sure what you meant on your initial reply but I soon realised through your comment I could simply check if the field has a value in it. Thanks.

user3411176 Over a year ago

Peter, If hypothetically I had dozens of update commands in series like the above, and they were quite resource intensive. If I found a match on the first update is there any other way to skip the other updates without a subsequent query to check the field had been updated?

PeterRing Over a year ago

The update will skip the already updated rows because of the FLAG is null. You do not need to check them. The only thing what you need to do is to commit them. So basicaly: BEGIN TRAN; UPDATE; COMMIT TRAN

Pouya Kamyar · Accepted Answer · 2016-10-24 13:05:03Z

0

you can put your update in try/catch and insert your result to another table

answered Oct 24, 2016 at 13:05

Pouya Kamyar

1331 silver badge9 bronze badges

Collectives™ on Stack Overflow

MS SQL - Conditional UPDATE return result

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related