Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 20-Aug-10 18:42pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
Varun Sareen at 22-Aug-10 2:25am
   
Reason for my vote of 4 I applied the same (cursor) dear atlast..Thanks for the same.
aspdotnetdev at 30-Aug-10 13:26pm
   
Sandeep, there is no need for a cursor. This can be handled with a simple UPDATE. See my answer.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

DECLARE @IncrementValue int
SET @IncrementValue = 1
UPDATE Table1 SET Column1 = Column1 + @IncrementValue
  Permalink  
Comments
Sandeep Mewara at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

UPDATE [TABLENAME] SET [COLUMNNAME] =  [COLUMNNAME] + 1
 
For decrement
 
UPDATE [TABLENAME] SET [COLUMNNAME] = [COLUMNNAME] - 1
  Permalink  
v3
Comments
Varun Sareen at 30-Aug-10 13:04pm
   
Reason for my vote of 1 bad :)
Toli Cuturicu at 30-Aug-10 13:07pm
   
Reason for my vote of 1 no
aspdotnetdev at 30-Aug-10 13:12pm
   
Right idea, incorrect sytax. See my answer.
hiren soni at 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)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 397
1 OriginalGriff 380
2 Shai Vashdi 280
3 Emre Ataseven 135
4 Abhinav S 128
0 Sergey Alexandrovich Kryukov 8,894
1 OriginalGriff 5,200
2 Peter Leow 3,955
3 Maciej Los 3,535
4 Abhinav S 3,208


Advertise | Privacy | Mobile
Web04 | 2.8.140415.2 | Last Updated 4 Oct 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid