SQL TIP: Auto Increment in an UPDATE statement.

• Feb 28, 2004
• 30 Comments
• Print

I needed to create a temp table in SQL with a column that contained an incrementing integer, without making that column an identity.

For example, suppose I want to select record from a table of users, but add a column that contains an incrementing counter. The data in the table should look like so:

To start, I can run the following query to create the temp table:

SELECT counter = 0, * FROM Users INTO #tmp_Users

This creates a temp table named #tmp_USers where the column "counter" is set to 0 for each row.

In order to update the counter column so that each row has a counter value greater than the previous row, run the following statement.

DECLARE @counter int
SET @counter = 0
UPDATE #tmp_Users
SET @counter = counter = @counter + 1

If you’re looking to master your T-SQL Fu, I recommend this book. It provides a great reference for advanced T-SQL querying techniques. Keep in mind that this book is not for beginners. It gets into some deep analysis of how querying works in SQL 2005

What others have said

Jon Mar 27, 2004 9:29 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

You are a star - This will save me so many cursors yolu would not believe. Very simple. Very neat. Cant understand why I didnt think of it before. Can I have your babies?

Haacked Mar 31, 2004 12:19 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Sure buddy. Sure.

Shane Calhoun Jun 08, 2004 11:36 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

muchas gracias, that was very helpful.

Jon Apr 06, 2006 3:25 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Any way of doing this as part of an insert statement? The following doesn't work:

DECLARE @counter int
SET @counter = 0

insert into (#tmp_Users ID,col1,col2...)
select (SET @counter = counter = @counter + 1),col1,col2... from AnotherTable

r0n Jul 25, 2006 7:48 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

very informative. got my auto increment start at a specific number that i wanted. many thanks

Fry Aug 15, 2006 9:17 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

very informative indeed but how would you go about starting your increment based on data from another table?

Haacked Aug 15, 2006 9:35 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

I'm not sure what you mean. If you want to set the starting value, you'd change the following line:

SET @counter = 0

to

SELECT @counter = MAX(ID) FROM MyOtherTable

or something like that.

Pascal Oct 10, 2006 1:04 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Excellent! Time saver!

Alex Zach Nov 01, 2006 11:17 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

You dont need to create a temp table

DECLARE @i As int
SET @i = 1

UPDATE USERS
SET @i= Counter = @i+1

Haacked Nov 01, 2006 11:23 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

I didn't need the temp table to do the auto-increment, I needed the temp table for another reason. That was the GOAL of that task.

My UPDATE statement is the same as yours, essentially. The only difference was that I was updating the temp table.

snuffnit Dec 01, 2006 8:50 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Life Saver Man !!!

Zazza Jan 12, 2007 10:07 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Great!!!!

Do you know any method for doing the same thing in a Select statement?
I would something like this:

select {increment value} as Counter, strUsername from Users

Counter strUsername
----------------------
1 John
2 Mike
3 Laura
...

hankx in advance

sathya Feb 08, 2007 7:11 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

kewl
it saved awful coding and my little brain from exhaustion ;)

cheers
sathya

bilal Jul 04, 2007 2:00 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

how can i make an increment statmen in sql

jubican Aug 02, 2007 4:25 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

This is a great method, but it does not work when you need to reorder just a handful of records out of a table. Any quick ideas on that?

Thanks!

Similar issue but ... Sep 13, 2007 11:06 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

I am trying to do something similar, but my two fields are of different types. Note @Cnt is of type decimal and the field "ItemNumber" from table @myTable is decimal(18,)... The field type cannot be changed so...

DECLARE @Cnt DECIMAL
UPDATE [myTable]
SET @Cnt = ItemNumber = @Cnt + 1
WHERE ( OrderNumber = <some order #> )

I get the following error: "Data type numeric of receiving variable is not equal to the data type decimal of column 'ItemNumber' "... even though @Cnt is a decimal field!

Help!

Mei-Hsia Chen Oct 25, 2007 8:19 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

"Do you know any method for doing the same thing in a Select statement?
I would something like this:

select {increment value} as Counter, strUsername from Users

Counter strUsername
----------------------
1 John
2 Mike
3 Laura
...
"
The above question was asked by Zazza on Jan 12, 2007. I am having the same creating counter problems. It would be great if you have solutions for this case. This is a great site.

Thanks. Mei-Hsia

PJ Dec 11, 2007 11:04 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

I need to create a increment counter on a phone number in SQL. It would increment/count the phone number by 1 until the number changed within a list and then start the increment over on the new phone number. (Would this need a loop?) Thanks, PJ

Adam Dyler Dec 14, 2007 3:26 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

haha!! amazing!! :-)

Ben G. Jan 08, 2008 2:24 PM

# Queue up the Nobel Prize

I hereby nominate Phil Haack for the 2008 Nobel Prize for SQL Tips!!

Sitra A. Feb 01, 2008 7:25 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

This is the coolest tip ever!! Thank you.
I would like to make a correction through. On the first example
SELECT counter = 0, * FROM Users INTO #tmp_Users
Should really be
SELECT counter = 0, * INTO #tmp_Users FROM Users

Monica Apr 08, 2008 11:59 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

You TOTALLY ROCK for posting this

Aidanb Apr 29, 2008 5:18 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Nice and Simple :-)

Ben Harper May 16, 2008 9:38 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Ahhh, bloody fantastic.
Simple, but not obvios.
Well done champ.

Phil Jun 25, 2008 1:01 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

WOW, what a great help! With you help I found how to Update the target field with the sum of the updated value the orignal.


UPDATE Cart
SET CartQnty = CartQnty + @CartQnty
WHERE (OrderNo = @OrderNO) AND (ProdID = @ProdID)


MANY THANKS

Shin Jun 26, 2008 6:09 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

This is great!!
I have an answer for PJ's question to count each phone number. I needed to do the same and I modified a little. It worked.
Make sure the records are sorted by Phone Number.
Then declare Phone Number variable.

Delcare @intCounter
Declare @strPhone varchar(12)

Update X
Set @intCounter = counter = Case When @strPhone <> Phone Then 0 Else @intCounter End + 1, @strPhone = Phone
From MyTable X

Updating the Phone field is redundant but it allows to store the value in the memory.

Cherikooka Jul 08, 2008 12:27 PM

# re: SQL TIP: Auto Increment in an UPDATE statement.

I LOVE YOU! Thank you so much....this little tip solved an irritating problem involving random photos on our Intranet.

Ramana Jul 16, 2008 11:48 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

One of the greatest pieces of code ever seen!! Amazing trick to save tons of brain work!

Ketan Aug 25, 2008 12:34 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

Awesome tip. Great work!!!

Seb Aug 29, 2008 9:21 AM

# re: SQL TIP: Auto Increment in an UPDATE statement.

"Do you know any method for doing the same thing in a Select statement?
I would something like this:

select {increment value} as Counter, strUsername from Users

Counter strUsername
----------------------
1 John
2 Mike
3 Laura
...
"
Here is the solution:

create table #user
(
UserID int,
Username varchar(255)
)

insert into #user values (10, 'John')
insert into #user values (20, 'Mike')
insert into #user values (30, 'Laura')

select (
SELECT COUNT(UserID) + 1 AS Counter
FROM #user usr
WHERE usr.UserID < #user.UserID
) AS Counter,
Username
from #user

drop table #user

What do you have to say?

Title:

Name:

Email: (never displayed) (will show your gravatar)

Website:

Comment: Allowed tags: blockquote, a, strong, em, p, u, strike, super, sub, code

Please add 3 and 2 and type the answer here: