Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more: , +
Dear Friends,

I am facing a problem related to SQL query. I need to update a table column values (data type 'int') in increment/decrement on single update query. Like..there are values in column1 as:-

Column1

1
2
3
4
.
.
.

The result should be

Column1

2
3
4
5
.
.
.
OR

Column1

0
1
2
3
.
.
.

Thanks
Posted
Updated 31-May-22 20:56pm

SQL
DECLARE @IncrementValue int
SET @IncrementValue = 1
UPDATE Table1 SET Column1 = Column1 + @IncrementValue
 
Share this answer
 
Comments
Sandeep Mewara 30-Aug-10 13:31pm    
Agreed. But this is for simple +1 known value addition. If OP was looking for just single fixed increment then this should do.
I considered it was a problem statement formulated and suggested him a generic way to handle it. If it was a dynamic value or different for all or some computation before adding then Curson would do.

Lastly, I would say before you point out: Cursors are not good from performance point of view. I know. :)
AspDotNetDev 30-Aug-10 14:21pm    
This will work for any increment amount (that's why I used a variable, to make that clear), so long as the same increment amount is used for each column. There is nothing in the question to suggest otherwise. Also, an UPDATE would work if a JOIN can be done against another dataset with variable increment values. It is only when subsequent increments change based on previous rows that a cursor may be required. A cursor would work, but it is an overly complicated and slow solution that does not teach the OP the best practice of taking the simplest approach.
Sounds like you need SQL CURSORS.
Have a look here to read all about the: Cursors (Transact-SQL)[^]

In a nutshell, using cursors, you can loop through all the rows of table and thus in the loop you can modify the values.
 
Share this answer
 
Comments
Varun Sareen 22-Aug-10 2:25am    
Reason for my vote of 4
I applied the same (cursor) dear atlast..Thanks for the same.
AspDotNetDev 30-Aug-10 13:26pm    
Sandeep, there is no need for a cursor. This can be handled with a simple UPDATE. See my answer.
update   mytable
  set    IDSEQN_NUMB = rownum
  where  IDSEQN_NUMB is null
 
Share this answer
 
Create procedure SP_Update_Points
(@SplID int,@IncrPoints nvarchar(50))

As
Begin

Update tblSpeciallists set Points=CAST(Points As int)+@IncrPoints
where SpeciallistID=@SplID

End

--Exec SP_Update_Points 1,3

-- Use this procedure for increasing column value at runtime using Update command
 
Share this answer
 
SQL
UPDATE [TABLENAME] SET [COLUMNNAME] =  [COLUMNNAME] + 1


For decrement

SQL
UPDATE [TABLENAME] SET [COLUMNNAME] = [COLUMNNAME] - 1
 
Share this answer
 
v3
Comments
Varun Sareen 30-Aug-10 13:04pm    
Reason for my vote of 1
bad :)
Toli Cuturicu 30-Aug-10 13:07pm    
Reason for my vote of 1
no
AspDotNetDev 30-Aug-10 13:12pm    
Right idea, incorrect sytax. See my answer.
hiren soni 30-Aug-10 13:40pm    
SORRY!!! I WROTE A WRONG UPDATE QUERY. BUT NOW ITS DONE

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900