UPDATE question

Question

UPDATE question

eeckhaut

SSC Veteran

Points: 212
More actions
February 11, 2009 at 6:52 am

#205324

Hello,
I want to update several rows in 1 UPDATE statement, this is what I want to do:
The column 'counter' of every row should have a unique incrementing value, starting from a value I provide.
ex: Updating 5 rows starting from 250, so values for column 'counter' will be 'C250', 'C251', 'C252','C253','C254'
For information: this value 250 is the MAX(counter) from the table 'tblcounter'
Can this be done in 1 statement?
thanx in advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

just to be clear, an UPDATE statement will affect EVERY row in a table, UNLESS you limit it with a WHERe statement; This is one of the cool principals of SET based updates.

in your case,

you want to do something like

UPDATE tblcounter

SET counter = 250

WHERE SOMECOLUMN IN('C250', 'C251', 'C252','C253','C254')

without knowing more, I dunno if that's what you are after..i really need the actual CREATE TABLE statement so i know the structure, and the sample data sow I can tell what you really want to update.

eeckhaut (2/11/2009)
Hello,
I want to update several rows in 1 UPDATE statement, this is what I want to do:
The column 'counter' of every row should have a unique incrementing value, starting from a value I provide.
ex: Updating 5 rows starting from 250, so values for column 'counter' will be 'C250', 'C251', 'C252','C253','C254'
For information: this value 250 is the MAX(counter) from the table 'tblcounter'
Can this be done in 1 statement?
thanx in advance

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post table structure, sample data and desired result.

Thanks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

eeckhaut (2/11/2009)
Hello,
I want to update several rows in 1 UPDATE statement, this is what I want to do:
The column 'counter' of every row should have a unique incrementing value, starting from a value I provide.
ex: Updating 5 rows starting from 250, so values for column 'counter' will be 'C250', 'C251', 'C252','C253','C254'
For information: this value 250 is the MAX(counter) from the table 'tblcounter'
Can this be done in 1 statement?
thanx in advance

[font="Verdana"]I'm thinking that you want an increasing counter to match the counter stored in your "tblcounter". So when you asssign counter values to your table, they also have to change within your counter table.

I don't see how you can do that in one statement. The way I would do it is:

lock the counter table

get the current counter value

assign the new counter values (you can do this bit in an update statement)

write back the last counter value assigned (this should be one more than went to the update)

commit

So long as your update statement isn't too huge, the locking shouldn't be too much of an issue.

[/font]