Updating a column with serial numbers

  • I want to update a table's column data with serial number i.e. first row 1, 2nd row 2, 3rd row 3 etc.

    I now to generate serial number in resultset using ROW_NUMBER() function but how can I update a table with it.

  • Another method to do it ...

    Add the column you want to store serial numbers as identity column

    Alter table table1 Add serialnumner INT IDENTITY(1,1)

    "Keep Trying"

  • Do you have any columns that have unique values. For using the rownumber function unique columns will be required to make the joins.

    a very basic example...

    create table #t (col1 varchar)

    INSERT INT O #t values ('c')

    INSERT INT O #t values ('d')

    INSERT INT O #t values ('e')

    update #t set col2 = rownum

    FROM (select col1,row_number() over (order by col1) as rownum from #t) D

    WHERE D.col1 = #t.col1

    col1 contains unique values so its possible to make the join on col1.

    "Keep Trying"

  • Thanks for your response.

    I know about the identity field, but I have to update serial number only for selected records and its not a primary key field.

    Following query can help you understand the requirement. I'm trying to do it but its not working

    UPDATE tab1 SET seqCol = (SELECT ROW_NUMBER() OVER (ORDER BY CreationDate) FROM tab1 where FK_ID=6) where FK_ID=6

  • create temp table usign Identity column and then fill the data into this temp table.

    Now you have the seq. data into the temp table, now it's time to update the base table based on primary field.

    hope this will help you.

  • is created date unique for each record in that set of records.

    "Keep Trying"

  • Isn't there any other way without creating temporary table i.e. using new features in SQL 2005/8 CTE etc.

  • You can update a derived table:

    UPDATE D

    SET seqCol = RowId

    FROM

    (

    &nbsp&nbsp&nbsp&nbspSELECT ROW_NUMBER() OVER (PARTITION BY FK_ID ORDER BY CreationDate) AS RowId

    &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,seqCol

    &nbsp&nbsp&nbsp&nbspFROM tab1

    &nbsp&nbsp&nbsp&nbspWHERE FK_ID = 6

    ) D

  • If using row_number() make sure you have "uniqueness" in your over() part or the results can be non-deterministic!


    * Noel

  • It sounds to me like what you need is a view with a row_number column, instead of adding a column to the actual table. That way, you can include a Where clause in it, and whatever level of complexity you need for the row_number data.

    With that, you should be able to get what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes a view is a nice idea but it will work only if the serial numbers dont need to be presisted in the database.

    "Keep Trying"

  • Thanks for your support. I like your query but there's a problem.

    I didn't execute your query yet because I didn't find place to put WHERE clause in UPDATE statement and I'm afraid without WHERE it may update all rows in table.

    Actually I want to update only rows fullfilling specific criteria with serial numbers. I hope you understand my problem.

  • Try this in your test environment.

    UPDATE D

    SET seqCol = RowId

    FROM

    (

    SELECT ROW_NUMBER() OVER (PARTITION BY FK_ID ORDER BY CreationDate) AS RowId

    ,seqCol

    FROM tab1

    WHERE FK_ID = 6

    ) D

    -- where clause

    WHERE "your condition"

    "Keep Trying"

  • Is this what you are looking for?
    select row_number() over (partition by rand() order by rand() )  From <table_name>

  • microsoftee - Monday, February 23, 2009 12:04 AM

    I want to update a table's column data with serial number i.e. first row 1, 2nd row 2, 3rd row 3 etc.I now to generate serial number in resultset using ROW_NUMBER() function but how can I update a table with it.

    I don't think you know what a table or serial number is. Do you have any manufactured products sitting around reading see it? Look for the serial number on your phone, radio or whatever. Serial numbers are by definition assigned by an external source and have nothing whatsoever to do with the "physical position" of rows in the table. The serial numbers may include letters and digits, punctuation marks and check digits. Designing a serial number can be tricky and requires careful thought.

    It looks like you're trying to sequentially number a table. But that's absurd! Rows have no ordering by definition! So there's no such thing as a first row, second row, etc. This is usually covered the first week of any class on relational databases. In fact, one of the basic principles of RDBMS is that rows are located not by a physical position, but by a logical key. Exactly what are you trying to do with this table?

    Part of the serial number can be a sequence (please look up the CREATE SEQUENCE statement ore read https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/).

    Please post DDL and follow ANSI/ISO standards when asking for help. 

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply