question

Joe_Hell avatar image
Joe_Hell asked

increment an int column via TSQL

Hello, I table with an int column. It is populated with data. What I need to do is change the value of the int column row 1 to 200 row 2 to 400 row 3 to 600. I want to do this via tsql. Not an identity column. I have tried CREATE TABLE [dbo].[table1]( [A] [int] NOT NULL ) ON [PRIMARY] GO declare @var2 int declare d1 cursor for select * from joe open d1 fetch next from d1 into @var2 While @@FETCH_STATUS = 0 begin declare @var1 int select @var1 = MAX(a) + 200 from joe --@var1 = select MAX(a) + 200 from joe update joe set A = @var1 where A = @var2 - 200 fetch next from d1 into @var2 end CLOSE d1 DEALLOCATE d1 But this ends up updating all records. I know I am just not thinking about this correctly. Please someone give me a kick to jumpstart my brain. TIA
tsql
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
So you don't want to update all the records? How do you determine which records to update?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Your question says you want row 1 to be 200, row 2 to be 400 and row 3 to be 600 but in your code it looks like you are adding 200, not multiplying. Can you explain this more clearly please? Maybe give us some data to work with as an example. If you simply want to multiply the values in a column by 200 then this should do what you need: Update Joe Set a = a * 200 wrap it in a transaction and check the results before you `COMMIT` and you can check it's done what you need
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
So all the rows currently have the same value? The cursor code is not needed and is misleading, regardless of which table it refers to.
2 Likes 2 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Fatherjack nicely pointed out the value of transactions, but an added step that can be useful is to have a testDb (or better, a test Server) where you can make sure your code does exactly what you want before it touches production. Backups with point-in-time-restore-capability are also your friend, though those are somewhat less useful in large databases that may have multiple simultaneous users.
1 Like 1 ·
Joe_Hell avatar image Joe_Hell commented ·
Thanks for the response The next value in the column needs to be incremented by 200. Not multiplied by 2. if you need to populate with data. insert into table1 values (200) go 40 the cursor was referencig the wrong table here it is corrected declare @var2 int declare d1 cursor for select * from table1 open d1 fetch next from d1 into @var2 While @@FETCH_STATUS = 0 begin declare @var1 int select @var1 = MAX(a) + 200 from table1 --@var1 = select MAX(a) + 200 from table1 update table1 set A = @var1 where A = @var2 - 200 fetch next from d1 into @var2 end CLOSE d1 DEALLOCATE d1
0 Likes 0 ·
Joe_Hell avatar image Joe_Hell commented ·
Yes all the rows have the same value. Again just trying to increment each value by 200. I apologize if the cursor code was misleading if a cursor is not needed my mistake. Honestly just looking for the easiest way to update this data via TSQL.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
OK, so the table must have other columns and one of those is what you want to use to order the updates? If not then you need to pick one. I use ColB in the example below USE [CentralDB] IF OBJECT_ID('tempdb..#table1') IS NOT NULL DROP TABLE #table1 GO CREATE TABLE #table1 ( ColA INT, ColB INT ) go INSERT [#table1] ( [ColA] , [ColB] ) SELECT TOP 40 200 , ABS(CHECKSUM(NEWID())) % 100 + 1 FROM [sys].[columns] AS c GO SELECT * FROM [#table1] AS t; WITH datas AS ( SELECT [t].[ColA] , [t].[ColB] , ROW_NUMBER() OVER ( ORDER BY [t].[ColB] ) AS r_n FROM [#table1] AS t ) UPDATE [#table1] SET [ColA] = [datas].[r_n] * 200 FROM [datas] INNER JOIN [#table1] AS t ON [datas]. [ColB] = [t].[ColB] SELECT * FROM [#table1] AS t ORDER BY [t].[ColB]
0 Likes 0 ·
Robert L Davis avatar image
Robert L Davis answered
Here is a set based solution for you. I added the insert statement just to have some data to test with. CREATE TABLE [dbo].[table1]( [A] [int] NOT NULL ) ON [PRIMARY] GO Insert Into dbo.table1 Values (1), (2), (3), (4); Go If OBJECT_ID('tmpTable1') Is Not Null Drop table dbo.tmpTable1; Go Select A, IDENTITY(int, 200, 200) As tmpA Into dbo.tmpTable1 From dbo.table1 Order By A; Go Update t Set A = tmpA From dbo.table1 t Inner Join dbo.tmpTable1 tmpT On T.A = tmpT.A; Go If OBJECT_ID('tmpTable1') Is Not Null Drop table dbo.tmpTable1; Go
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Nice. Need to remember this way.
0 Likes 0 ·
daveh0ward avatar image
daveh0ward answered
Another possible set based solution using ROW_NUMBER(). Assumes the values in the original table are unique. CREATE TABLE [dbo].[table1]( [A] [int] NOT NULL ) ON [PRIMARY] GO Insert Into dbo.table1 Values (1), (2), (3), (4); UPDATE t1 SET A = t2.rn FROM dbo.table1 t1 JOIN (SELECT A, ROW_NUMBER() OVER (ORDER BY A) * 200 AS rn FROM dbo.table1) t2 ON t1.A = t2.A;
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
It also assumes (as my first answer did) that the values in A are start at 1 and increment by 1. OP says in comments that the values in A are all one value.
0 Likes 0 ·
daveh0ward avatar image daveh0ward commented ·
No, actually it only assumes that the values are unique. Robert's answer above has the same limitation. I missed the comment about all the rows in his table having the same value.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
sorry, yep, unique.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Another option (if you're in SQL Server 2012) is to use the SEQUENCE - however, it's a bit more complicated, as the sequence is not restricted to a single table. See http://msdn.microsoft.com/en-us/library/ff878091.aspx for more information.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Joe_Hell avatar image
Joe_Hell answered
Thanks everyone for the info
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.