Basic question on update statement

Question

Basic question on update statement

Julie Breutzmann

Hall of Fame

Points: 3637
More actions
July 29, 2008 at 2:00 pm

#382047

I'm relatively new to relational databases, so my question is rather basic. I've looked in BOL, but wasn't able to determine the answer.
If I have an update statement with multiple set statements, will the actual update be based on the original value of the data? For example:
Tbl_1
id_num, value_1, value_2
1, a, c
2, a, b
3, c, b
update tbl_1
set value_1 = value_2,
value_2 = value_1
from tbl_1
results:
tbl_1
id_num, value_1, value_2
1, c, a
2, b, a
3, b, c
Thanks!

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Yes. So long as both updates are in the same statement, like your example.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Julie Breutzmann Hall of Fame Points: 3637 More actions · Answer 2

Thanks, G-Squared. That's what I thought, but I wasn't positive. And yes, I was only asking if this was true if they were in the same update statement. I'm glad that you added that qualification, because it may not be obvious to everyone.

GSquared SSC Guru Points: 260824 More actions · Answer 3

On suggestion: Set up a database you can play around in.

I have a ProofOfConcept database in a copy of SQL 2005 Dev Edition, running on my desktop machine.

When I run into questions like this one, I try them out in that database. In your post, you were a few keywords away from writing test code.

Not that I mind answering such questions, but sometimes, when you explore the options yourself, you'll discover things that you otherwise wouldn't. And, it's kind of fun too.

I also take code that people post on this (and other) web pages, and run it in that database. Safer than testing it on a real server. Helps me learn new things and helps me answer questions for people.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Julie Breutzmann Hall of Fame Points: 3637 More actions · Answer 4

GSquared, thanks for the suggestion. I agree that one learns more when one takes the time to explore on ones own. I will certainly try to do that within the limits of my authority.

Julie

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Julie:

Think of it this way, the actual value of a database column does not change until it is written back to the database. This is done a row at a time, not a column at a time. Variables change immediately, so you can have update statements like this:

update #temp

set colB = @counter, @counter = colA = colA + @counter, colC = colA

In the update above, a new value for colA will be calculated, but it is not considered changed until the row is written back to the table. (If you were running another query against the table at the same time, you wouldn't expect to get the new value prior to the update being written, would you?)

The variable @counter will immediately be set to the new value of colA because variables exist only in memory and don't have to be written back to a table. ColB will have a new value calculated on the new value of @counter. But colC will have the value of colA as it was originally read in from the table.

I apologize if that reads clumsily. One important thing to see here is that it doesn't matter if you write the set statement as

set colB = @counter, @counter = colA = colA + @counter, colC = colA

or if you write it as

set colC = colA, @counter = colA = colA + @counter, colB = @counter

They both produce the same result.

Test this out for yourself. Create a #temp table with 10 rows and three int columns (colA, colB, colC.) Set the value of colA in each row and set the value of colB and colC to zero. Then run the following. A picture is worth a thousand words.

------------------------------------------------------------------------------

declare @counter int

set @counter = 0

update #temp

set cola =1, colB = 0, colC = 0

select * from #temp

update #temp

set colC = colA, colB = @counter, @counter = colA = colA + @counter

select * from #temp

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Julie Breutzmann Hall of Fame Points: 3637 More actions · Answer 6

Thanks, bhovious. I've been a programmer for a looong time and am trying to understand how this set-based stuff works. I initially started off in COBOL and Fortran and then moved on to other DBs, so this is quite a change. I'm making good progress through practice, SQL Server Central, etc.

Julie