Update From Table w/ Multiple Rows... How does SQL Choose?

Question

Post reply

Update From Table w/ Multiple Rows... How does SQL Choose?

David F

Old Hand

Points: 360
More actions
September 20, 2012 at 10:44 pm

#104172

Hi guys, I have a question, if I do an update statement from a table where multiple rows meet the criteria, how does SQL perform the operation? Does it update from all the rows that match and the last matching row is the result? Or does it stop after updating from the first row the matches??
For example... take a look at the following code:
[font="Courier New"]
Declare @tbCalc Table (ID Int Identity(1,1), keyID int, value int)
Declare @tbResult Table (baseValue Int, resultValue Int)
Insert Into @tbCalc( KeyID, Value)
Values (1,4),(2,9),(3,1),(1,8),(2,6),(3,7),(2,3)
Insert Into @tbResult ( baseValue )
Values (7)
Update @tbResult Set
resultValue = baseValue * tc.value
From @tbCalc tc
Where 1=1
And keyID = 2
Select * from @tbResult[/font]
The final result is a table with the resultValue of 63
So why did SQL end up multiplying 7 * 9 instead of say 7 * 6 or 7 * 3.... since all of those other values also have the keyID of 2
Can anyone edumacate me?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kl25 Hall of Fame Points: 3752 More actions · Answer 1

Check the first example in the 'Best Practices' section for the Update statement from msdn:

Update (Transact-SQL) - Link to the MSDN page

Your example is very similar to the one following the paragraph that starts 'Use caution..' and goes on to explain how determinism affects the results of an update statement.

David F Old Hand Points: 360 More actions · Answer 2

So if I'm reading that correctly the answer is... "I dont know"... There is no defined rule for SQL to determine how to proceed, it very well could appear random in nature.

Is that what you are reading as well??

Thank you for that link BTW... good info!

kl25 Hall of Fame Points: 3752 More actions · Answer 3

Right. Hence the words of caution. The update statement must be written deterministically or you'll end up with the situation you described in your example.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

David F (9/21/2012)
So if I'm reading that correctly the answer is... "I dont know"... There is no defined rule for SQL to determine how to proceed, it very well could appear random in nature.
Is that what you are reading as well??
Thank you for that link BTW... good info!

The short answer is - "whatever is most expedient to meet the query at the time of execution". Which is unfortunately where determinism comes in: if you leave some wiggle room (where it COULD choose any one of several values to use), chances are it will take advantage of that wiggle room (i.e. sometimes using one, and sometimes using another).

If you don't want any variability, just make sure that your query only allows for one match per record updated.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

kl25 Hall of Fame Points: 3752 More actions · Answer 5

Thanks Matt. That's a much better way to explain it.

David F Old Hand Points: 360 More actions · Answer 6

Matt Miller (#4) (9/21/2012)
The short answer is - "whatever is most expedient to meet the query at the time of execution". Which is unfortunately where determinism comes in: if you leave some wiggle room (where it COULD choose any one of several values to use), chances are it will take advantage of that wiggle room (i.e. sometimes using one, and sometimes using another).
If you don't want any variability, just make sure that your query only allows for one match per record updated.

Thanks, that makes sense, and just to be clear, I'm not asking because I want to do this, obviously, there should only be one match, but I'm taking over an app built by another developer, and this has been in production for a while now and I've found that his query has this issue, so I wanted to be able to tell the application admins what they were likely seeing due to this flaw in the query. Turns out I'm going to have to tell them, we just can't say for certain how SQL has been determining which data point to use. But going forward, I will, of course, fix the query.

Thanks again.