Post reply

update my count

  • April 1, 2008 at 8:02 am

    #379525

    I have a column in my SQL database created for the sole purpose of being able to identify rows. I have numbered the entries in this column in numeric order. Can someone provide me and example of update count script targeting the identified column. 😀

  • April 1, 2008 at 8:16 am

    #796517

    Hi,

    You can use Identity command. Example shown below.

    CREATE TABLE tmp(empno Identity(1,1))

    In the above example the column empno is used as identity and hence whenever a data is entered into the table empno column will be automatically incremented one more value and it goes on..

    Regards..Vidhya Sagar
    SQL-Articles

  • April 1, 2008 at 8:49 am

    #796559

    the script does not count down the column and insert the correct numerical number. Let me explain in detail

    I have nine columns and 1164 rows.

    One of the columns is just for number each row. I want to just update that column as needed. So I need the script to start from the first row and count down to the last row then put the results in the afore mention column. 😀

  • April 1, 2008 at 9:11 am

    #796589

    note that you need to ORDER BY this identity column to get rows back in numerical order. By default SQL Server does not guarantee row ordering, regardless of what order you insert them in.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • April 1, 2008 at 9:17 am

    #796600

    I must be missing something in your explanation. But I tried Insert, Update, and now Order By as you recommended but I get nothing but syntax errors when I run the script. 😀

  • April 1, 2008 at 9:24 am

    #796612

    Are you renumbering the rows after every insert, or are you looking for a means to update the row_number field as records are inserted? Can more than one row be inserted at a time?

    😎

  • April 1, 2008 at 9:37 am

    #796627

    I am looking for a way to update the row count through a script 😀

  • April 1, 2008 at 9:41 am

    #796630

    Okay, I guess we are missing the point, so show us. Please provide the DDL for the table, sample data (in the form of an insert statement with union all select statements), and what the expected results of the query you are saking for would be based on the given sample data.

    We don't need all the data, just a small sample.

    😎

  • April 1, 2008 at 9:56 am

    #796644

    I will try to oblige:

    The column is called Unique_Identifier. the data is just numbers 1,2,3,4, etc...My problem is if I delete any row then my numbers in that column gets off count. I just need a script that will go through that row and count down numerically and then place the results in that column.

    The column is not blank it already has data. The data type is int 😀

  • April 1, 2008 at 10:20 am

    #796663

    Are the row numbers the only way to identify the rows? Does the table have any other means to identify the order the data was entered?

    SAMPLE DATA, TABLE DDL, EXPECTED RESULTS; still need this to actually help you.

    😎

  • April 1, 2008 at 10:25 am

    #796667

    Yes it is the only way to identify the rows...this is to avoid any dups...

    Sample data provided each entry represents a column I just want to change the data in the last column

    Aurora Winfits Mortgages 5.5.5101 No Phone 2129060050 AMRS FICC Winfts Mortgages 6.2 is the latest. 48

    Aurora Winfits Options 4.9.4001 No Phone 2129060050 AMRS FICC Winfits Options 6.2 is the latest 49

  • April 1, 2008 at 11:13 am

    #796720

    How are new rows added to the table, and when do they get numbered?

  • April 1, 2008 at 11:21 am

    #796729

    The rows get added manually the numbers get added manually...

  • April 1, 2008 at 11:37 am

    #796745

    Use this as a basis for writing your update query:

    create table #MyTable(

    RowId int,

    RowDataCol1 char(10)

    )

    go

    insert into #MyTable (RowId, RowDataCol1)

    select 1, 'Data 1' union

    select 2, 'Data 2' union

    select 3, 'Data 3' union

    select 5, 'Data 5' union

    select 6, 'Data 6'

    go

    select * from #MyTable

    go

    with NewNumbers (

    NewRowId,

    OldRowId

    ) as (

    select

    row_number() over(order by RowId) as RowNumber,

    RowId

    from

    #MyTable

    )

    update #MyTable set

    RowId = NewRowId

    from

    NewNumbers nn

    inner join #MyTable mt

    on (mt.RowId = nn.OldRowId)

    go

    select * from #MyTable

    go

    drop table #MyTable

    go

  • April 1, 2008 at 11:49 am

    #796755

    How come I can not use update instead of create. I do not wish to create a new table, I just want to update the column?

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

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