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 19:42pm
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 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 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
0 OriginalGriff 200
1 George Jonsson 175
2 Kornfeld Eliyahu Peter 169
3 PIEBALDconsult 110
4 Richard MacCutchan 85
0 OriginalGriff 6,080
1 DamithSL 4,648
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,624
4 Sergey Alexandrovich Kryukov 3,294


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100