SQL TIP: Auto Increment in an UPDATE statement.

code 0 comments suggest edit

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:

counter UserID Email
0 3432 BillBrasky@example.COM
1 7913 zoolander@example.com
2 8372 donaldtrump@example.com

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

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.

Comments

avatar

88 responses

  1. Avatar for Jon
    Jon March 26th, 2004

    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?

  2. Avatar for Haacked
    Haacked March 30th, 2004

    Sure buddy. Sure.

  3. Avatar for Shane Calhoun
    Shane Calhoun June 8th, 2004

    muchas gracias, that was very helpful.

  4. Avatar for Jon
    Jon April 5th, 2006

    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

  5. Avatar for r0n
    r0n July 25th, 2006

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

  6. Avatar for Fry
    Fry August 15th, 2006

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

  7. Avatar for Haacked
    Haacked August 15th, 2006

    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.

  8. Avatar for Pascal
    Pascal October 10th, 2006

    Excellent! Time saver!

  9. Avatar for Alex Zach
    Alex Zach November 1st, 2006

    You dont need to create a temp table
    DECLARE @i As int
    SET @i = 1
    UPDATE USERS
    SET @i= Counter = @i+1

  10. Avatar for Haacked
    Haacked November 1st, 2006

    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.

  11. Avatar for snuffnit
    snuffnit November 30th, 2006

    Life Saver Man !!!

  12. Avatar for Zazza
    Zazza January 11th, 2007

    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

  13. Avatar for sathya
    sathya February 7th, 2007

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

  14. Avatar for bilal
    bilal July 4th, 2007

    how can i make an increment statmen in sql

  15. Avatar for jubican
    jubican August 2nd, 2007

    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!

  16. Avatar for Similar issue but ...
    Similar issue but ... September 13th, 2007

    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!

  17. Avatar for Mei-Hsia Chen
    Mei-Hsia Chen October 24th, 2007

    "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

  18. Avatar for PJ
    PJ December 11th, 2007

    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

  19. Avatar for Adam Dyler
    Adam Dyler December 13th, 2007

    haha!! amazing!! :-)

  20. Avatar for Ben G.
    Ben G. January 8th, 2008

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

  21. Avatar for Sitra A.
    Sitra A. January 31st, 2008

    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

  22. Avatar for Monica
    Monica April 8th, 2008

    You TOTALLY ROCK for posting this

  23. Avatar for Aidanb
    Aidanb April 28th, 2008

    Nice and Simple :-)

  24. Avatar for Ben Harper
    Ben Harper May 16th, 2008

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

  25. Avatar for Phil
    Phil June 24th, 2008

    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

  26. Avatar for Shin
    Shin June 26th, 2008

    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.

  27. Avatar for Cherikooka
    Cherikooka July 8th, 2008

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

  28. Avatar for Ramana
    Ramana July 16th, 2008

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

  29. Avatar for Ketan
    Ketan August 24th, 2008

    Awesome tip. Great work!!!

  30. Avatar for Seb
    Seb August 28th, 2008

    "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(
    SELECTCOUNT(UserID) + 1 AS Counter
    FROM#user usr
    WHEREusr.UserID < #user.UserID
    ) AS Counter,
    Username
    from #user
    drop table #user

  31. Avatar for mahroo
    mahroo October 20th, 2008

    Thanks a lot :) you saved meeeeeeeeeeeeeeeeeeeeeeeee .

  32. Avatar for trisith
    trisith October 20th, 2008

    Thanks for your sharing.
    I want to share somethings.
    Select IDENTITY(smallint, 1, 1) as counter, username
    from #user;
    OR
    Select IDENTITY(smallint, 100, 1) as counter, username
    from #user;
    OR
    Select IDENTITY(smallint, 1, 5) as counter, username
    from #user;
    have a nice day.
    cheer.

  33. Avatar for Amit
    Amit December 2nd, 2008

    The IDENTITY function can only be used when the SELECT statement has an INTO clause.

  34. Avatar for oadin
    oadin December 3rd, 2008

    For the select where you want :
    select {increment value} as Counter, strUsername from Users
    Counter strUsername
    ----------------------
    1 John
    2 Mike
    3 Laura
    ...
    Why don't you just use :
    select row_number() over (order by strUsername), strUsername from Users
    It's sql server 2005-2008 limitations

  35. Avatar for Yova
    Yova February 18th, 2009

    Excellent script!!!!

  36. Avatar for QuickChuck
    QuickChuck March 8th, 2009

    Im changing my autoincrement column to manual incrementing so it becomes an accurate count. I renamed the column to old_column and removed the identity setting. I dropped the existing indexes and created a clustered index on the old_column. I then ran above script and the new column has values in the same order as the old_column but without the holes left due to the identity functionality. The application will now increment the column.
    Thanks for the original code!

  37. Avatar for PRIYA
    PRIYA March 20th, 2009

    How do you autoincrement in update statement for a dynamic table using stored procedure

  38. Avatar for Wilfred Phua
    Wilfred Phua March 27th, 2009

    Does this apply to mySQL?

  39. Avatar for Felipe Lopez
    Felipe Lopez April 3rd, 2009

    Excellent!!! at last found the script, it is simpler than thought.
    Thanks, Bless

  40. Avatar for Digin
    Digin December 20th, 2009

    SET @temp =99;
    UPDATE table SET id = @temp := ( @temp +1 );

  41. Avatar for DRP
    DRP December 29th, 2009

    alter table table_xyz add id_num int IDENTITY

  42. Avatar for Thomas
    Thomas January 11th, 2010

    Nice statements :)
    What index does the SQL Server follow doing these updates?
    I have a customer database that I want a counter to be implemented but I want the customer with lowest number become #1 and so on.
    Can I give a hint to the update statement which index to use?
    Or does SQL Server always use the primary key?

  43. Avatar for Amol Bhate
    Amol Bhate January 28th, 2010

    Thanks a lot, you've done a great job.
    I've updated my 1500+ records within a min

  44. Avatar for Matt
    Matt February 1st, 2010

    Worked perfect for my requirements! Thanks!

  45. Avatar for Shilpi
    Shilpi February 4th, 2010

    I need help i have am running for loop in my function and i have to increment that value by 1 every time.........can anybody help me its urgent

  46. Avatar for shakti
    shakti February 14th, 2010

    Very good,,
    it works for me

  47. Avatar for nathanael
    nathanael March 25th, 2010

    --Here is the answer of you question.
    declare @tbl table
    (
    uniquecolumn int
    );
    Insert into @tbl values (10)
    Insert into @tbl values (11)
    Insert into @tbl values (12)
    Insert into @tbl values (13)
    Insert into @tbl values (14)
    Insert into @tbl values (15)
    Insert into @tbl values (16)
    Insert into @tbl values (17)
    Insert into @tbl values (18)
    Insert into @tbl values (19)
    Insert into @tbl values (20)
    Insert into @tbl values (21)
    select * from @tbl
    declare @counter int
    set @counter=0
    Update @tbl
    set @counter = @Counter + 1,
    uniquecolumn=@counter

    select * from @tbl

  48. Avatar for Madan
    Madan April 13th, 2010

    Hi Friends
    Try this out
    IDENTITY(x,y) is how you use autonumber in SQL ( x = starting point, and y = increment by)

  49. Avatar for Dr Vet Cumpanasu Florin
    Dr Vet Cumpanasu Florin April 26th, 2010

    I will pass the value to be incremented:
    mysql_query("UPDATE sdig SET Pro = ".$_POST['dig_id']+1." WHERE ID = 1");

  50. Avatar for Joel
    Joel May 3rd, 2010

    Thanks for this post, saved me some brain cells.

  51. Avatar for McConnell Group
    McConnell Group May 4th, 2010

    This just increased my effeciency, I appreciate the help!
    Shawn

  52. Avatar for Prateek Dorwal
    Prateek Dorwal July 3rd, 2010

    Thank a lot man!!!!! u r a champ!!!

  53. Avatar for zaggle
    zaggle July 21st, 2010

    this is awesome... so so so awesome
    thank you for taking the time, you have NO idea how much time you have saved me...

  54. Avatar for Wasim
    Wasim August 22nd, 2010

    mine is different case:
    Please let me know what the results set will be in case we want incremental value with respect to the fields value:
    For example: I am doing a data migration project and in the query trhe rule says that if Field A is having 3 repetitive values for policy say pola, pola, pola then the field B should display as 1 for pola, 2 for polb, and 3 for polc. similarly if there are 4 repetitive values for polb are there then the field B will display 1 for polb, 2 for polb ... and so on.. i have struck here please help..

  55. Avatar for Architects in Bangalore
    Architects in Bangalore August 28th, 2010

    I finally decided to drop a comment, and let me tell you this is another very strong and powerful post. I’ve been reading through some of your previous posts and have been visiting your blog every now and then.

  56. Avatar for Veena V
    Veena V October 13th, 2010

    Excellent script to use in select clause.
    select row_number() over (order by strUsername), strUsername from Users
    Thanks!

  57. Avatar for Atul Runthala
    Atul Runthala November 18th, 2010

    @Seb, Great

  58. Avatar for Joms
    Joms December 9th, 2010

    Great.................

  59. Avatar for rajin
    rajin January 25th, 2011

    awesome!! exactly what I was looking for. Thanks!!!

  60. Avatar for Yuri
    Yuri February 2nd, 2011

    JUST AAAAAAAWESOOOOOOOOME! That piece of code helped me save hours of sql+php coding! Thanks a lot!!!

  61. Avatar for Ekta
    Ekta February 15th, 2011

    I have already created a table,now i want to modify a column to be auto-incremented...
    can u please suggest me some sql script.However the query "Alter table CDP_LANGUAGE add LAN_LANGUAGE_ID int IDENTITY(1,1) primary key;"
    doesn't work.

  62. Avatar for Trish
    Trish February 17th, 2011

    Can this be done using an formula in Excel??
    I need to increment the last column of a set of numbers that
    represent employee Job IDs. I need the goal value to
    begin at 00, and increment by one each time. But also not incrementing when the last value is a duplicate.
    So, my increment column would need to look at the first column, second column, and third column. If the rows share the same value in the first column, and unique numbers in the last column, then start at 00. If the value in the last column is a duplicate of the previous row, give it the same value. And when the value in the first column of a row changes, reset the goal value back to 00, and increment using the rules above.
    My columns are: Goal value would be:
    2222 12 8977 00
    2222 12 8988 01
    2222 12 8988 01
    2222 12 8989 02
    3333 04 8500 00
    3333 04 8501 01
    3333 04 8502 02
    3333 04 8502 02
    Thanks!!!

  63. Avatar for Khurram
    Khurram March 30th, 2011

    Amazing how internet works like a repository of information where an article from Feb 2004 has helped me today in March 2011. Thanks for a great tip :)

  64. Avatar for Robert
    Robert April 17th, 2011

    SIMPLE & BRILLIANT !!!

  65. Avatar for Yosep
    Yosep May 29th, 2011

    Brilliant..
    An article from Feb 2004 help me a lot in this May 2011.
    Your article and website is rock!
    Thanks for sharing and I can learn more in SQL it's all thanks to you :)

  66. Avatar for Bill
    Bill June 14th, 2011

    --Looking for Select, rather than Update?
    --Let's say you have a table AACustomers
    CREATE TABLE [AAcustomers](
    [First_Name] [varchar](50),
    [Middle_Name] [varchar](50),
    [Last_Name] [varchar](50))
    --and three Customers
    INSERT INTO [AAcustomers]([First_Name],[Middle_Name],[Last_Name])
    Select 'Joe', 'B', 'Jones' union All
    Select 'Jane', 'A', 'Adams' Union All
    Select 'Smokey', 'The', 'Bear'
    --For us dummies, a temp table is very handy for accomplishing this kind of work
    select
    Identity (int,1000,100)AS CustomerID
    ,First_Name
    ,Middle_Name
    ,Last_Name
    Into #a
    From AAcustomers
    Select * From #a
    ----drop Table #a --Just a little housecleaning

  67. Avatar for neuroxik
    neuroxik June 27th, 2011

    Is there a similar method for MySQL?

  68. Avatar for Ed
    Ed September 15th, 2011

    i have this data into my table:
    1John
    4Mike
    7Laura
    1John
    4Mike
    7Laura
    1John
    4Mike
    7Laura
    How do i make an UPDATE to remake the id's with the order of the row for example, or.. just incremental from 1 to last.?

  69. Avatar for Sonal
    Sonal September 27th, 2011

    i Know this is old post but this is simply amazing.

  70. Avatar for xyz
    xyz September 28th, 2011

    thank you... it helps

  71. Avatar for Renee
    Renee October 17th, 2011

    Pls how can I do the same thing in MYSQL?

  72. Avatar for Scott Ward
    Scott Ward October 20th, 2011

    Great tip.
    For me it did add the counter column in an unordered way, I have a date field in my table and when viewing the table the dates are ordered (without sorting) but the counter is all over the place compared to the date order.
    Here is my solution which allows some extra room for using an order by to add the counter in an ordered manner and also would allow limiting which records to get the counter, if needed.
    But you need a unique Date or other column for this to work
    (one option might be to use your SQL to add the counter ID column, then use my code to add a new column in the order you need it and only to the records you need it for, then remove the first counter column)
    --Add counter to all rows
    Update [Table] SET ID = tblTemp.ID
    FROM [Table] tblOriginal,
    (Select DateCol, Row_Number() OVER (ORDER BY DateCol) AS ID FROM [Table]) tblTemp
    WHERE tblOriginal.DateCol = tblTemp.DateCol
    --Add counter to Selected rows only
    Update [Table] SET ID = tblTemp.ID
    FROM [Table] tblOriginal,
    (Select DateCol, Row_Number() OVER (ORDER BY DateCol) AS ID FROM [Table] WHERE DateCol > '2010/01/01') tblTemp
    WHERE tblOriginal.DateCol = tblTemp.DateCol

  73. Avatar for Steve
    Steve November 7th, 2011

    Great Tip ... but does this work in PL/SLQ? I'm getting errors with the UPDATE's SET line.

  74. Avatar for Pradeep
    Pradeep November 21st, 2011

    This is a very useful post.
    Simple way to add auto increment field without looping through each record on the table.
    Thanks for sharing,
    Pradeep

  75. Avatar for Samyuktha
    Samyuktha December 7th, 2011

    Hi,
    I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
    My question is how to insert 400,000 rows at a time in a table?
    Can you let me know the script to generate it.
    Please do help me out.
    Thanks in Advance.

  76. Avatar for Pritam Sen
    Pritam Sen January 13th, 2012

    Howto i divied 1500 in 4 column in % format in runtime autometicaly.When i am insert 1500 the amount insert in 4 column as like 1 is 10%,2is 10%,3 is 35%,4 is 45% as.please infrom me urgently.

  77. Avatar for Abc
    Abc December 26th, 2012

    nice article....

  78. Avatar for sandy
    sandy April 15th, 2013

    Perfect Fix..Thanks buddy

  79. Avatar for SAM
    SAM May 3rd, 2013

    THANKS

  80. Avatar for Ankit
    Ankit July 26th, 2013

    You can always add a where clause to re-order a handful number of records

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

    where ....

  81. Avatar for She Told Me
    She Told Me January 9th, 2014

    wow! thanks a lot

  82. Avatar for Plaid the Imvailer
    Plaid the Imvailer March 10th, 2015

    Good stuff; very simple AND very useful! Thanks!

  83. Avatar for mohamed yasar
    mohamed yasar December 22nd, 2015

    i have one table in this table am using update query for auto increament greather than or equal to that value automatically increament that value will be stored to the another value

  84. Avatar for Dieguito28
    Dieguito28 August 29th, 2016

    Excelent! Tks.

  85. Avatar for Derek Klingman
    Derek Klingman September 9th, 2016

    This just saved me a ton of headaches and just help me improve a store procedures performance from minutes to seconds. It was using a loop to do this same idea, just row-by-row!!!! Thank you so much!

  86. Avatar for Tush
    Tush May 4th, 2017

    thanks a lot. with little modification as per requirement, it worked fine!

  87. Avatar for M Taj
    M Taj May 12th, 2017

    which book have you told to refer, please mention name of it

  88. Avatar for Ashraful
    Ashraful August 6th, 2018

    very helpful post.