Increasing the values in a column

Question

Increasing the values in a column

wing0t2

SSC Enthusiast

Points: 116
More actions
July 9, 2009 at 5:19 pm

#134181

Hi,
I am a novice to T-SQL and I believe I am not fully harnessing the power of SQL when I find my code full of loops and conditional statements, I am hoping you folks can point me in the right direction.
Here is the table I have,
CREATE TABLE tableA
(
id INT primary key,
year CHAR(10)
data CHAR(100) )
What I am trying to do is to add a constant number to values of the year column for all matching rows. e.g. change all the years to 4 year later by adding 4 to the year column.
Is there a easy way to do this instead of pulling each row out and adding 1 to every row?
Thanks in advance for any help

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

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

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

UPDATE dbo.tablea SET Year = CONVERT( int, Year ) + 1

I am counting on a number of facts here, I converted it to an integer, it will convert it back to a varchar for me. I also count on the fact that I want to change ALL records. If I don't I need a WHERE clause to restrict it.

Clear?

CEWII

wing0t2 SSC Enthusiast Points: 116 More actions · Answer 2

I added a where clause and it worked perfectly, I'm sure glad to throw out all the control statements and loops. Thanks a lot Elliott!

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

You are welcome, this is called a SET based operation, you performed the operatio on a whole set instead RBAR (Row-by-agonizing-row)..

CEWII

kramaswamy SSCoach Points: 18181 More actions · Answer 4

That being said, you should be careful about doing an update on a large number of rows, in a production database, since the operation will lock the rows that are being updated, and other operations against those rows will timeout until the update is complete. In the case of a simple update like yours, it's probably not an issue, but if your update would take a long time to run, you could risk losing other data in the process. If that's the case, then the approach of updating one record at a time is probably the better one, despite looking ugly.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

kramaswamy (7/10/2009)
If that's the case, then the approach of updating one record at a time is probably the better one, despite looking ugly.

Ummm... no... not if it's done correctly. Correctly means that you know the updates are prequalified and will work without failure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)