Post reply

Generating ID

  • October 26, 2005 at 1:36 pm

    #66828

    Guys,

    I have subset of data for which I need to make the id unique

    role seq    id modifiedid

    _________________________________

    1 1        10000 10000

    2 1        10000 10000

    1 1        10000 10001

    2 1        10000 10001

    1 1        10000 10002

    2 1        10000 10002

    In the above subset I have current values in the id, but I need to build a column 'modifiedid' where for each successive occurence of role 1 and 2, I should have the same 'modifiedid' values.

    I have tried using cursor but I have not been able to modify 2 successive rows at each instance.

    Any suggestions/inputs will be helpful

    Thanks

  • October 26, 2005 at 1:40 pm

    #600370

    What does that new id mean??

  • October 26, 2005 at 2:05 pm

    #600386

    right now as shown in the subset data only ID is populated, I need a way to populate 'modifiedid' column with the values shown in the example

    Thanks

     

  • October 26, 2005 at 2:16 pm

    #600394

    Repeating.... You ALREADY HAVE and id, what does that new id will mean???

  • October 26, 2005 at 2:28 pm

    #600399

    new id will allow me to display the single combination of role 1 and 2 with same id (new one), which is not possible with the existing id

    role seq          id              newid

    _____________________________

    1     1           10000       10001

    2     1           10000       10001

    Thanks

  • October 26, 2005 at 2:31 pm

    #600402

    Nevermind.. misread the data.

    Unless you have some other columns that can qualify as anique id, you're screwed. There won't be any set based way to do this.

  • October 26, 2005 at 3:29 pm

    #600414

    I created a tempid column and populated it as an identity column (this way I have a unique column) but still that doesn't work in my cursor any better ideas???

    role   seq      tempid     id         modifiedid

    __________________________________

    1       1         23       10000            10001

    2       1         24       10000            10001

  • October 26, 2005 at 7:40 pm

    #600437

    If there is no such PK column to provide you with the running unique value, you can also do this:

    create table testcase (role int, seq int, id int, modifiedid int)

    insert into testcase (role, seq, id) values (1, 1, 10000)

    insert into testcase (role, seq, id) values (2, 1, 10000)

    insert into testcase (role, seq, id) values (1, 1, 10000)

    insert into testcase (role, seq, id) values (2, 1, 10000)

    insert into testcase (role, seq, id) values (1, 1, 10000)

    insert into testcase (role, seq, id) values (2, 1, 10000)

    go

    select identity(int, 1, 1) as row_num, * into #temp1 from testcase where role = 1

    select identity(int, 1, 1) as row_num, * into #temp2 from testcase where role = 2

    update #temp1 set modifiedid = id + row_num

    update #temp2 set modifiedid = id + row_num

    go

    --start the transaction

    begin tran

    delete from testcase

    insert into testcase (role, seq, id, modifiedid)

    select role, seq, id, modifiedid from #temp1

    union all

    select role, seq, id, modifiedid from #temp2

    order by modifiedid, role

    select * from testcase

    --do the commit/rollback logic

    --output

    role seq id modifiedid

    ----------- ----------- ----------- -----------

    1 1 10000 10001

    2 1 10000 10001

    1 1 10000 10002

    2 1 10000 10002

    1 1 10000 10003

    2 1 10000 10003

    (6 row(s) affected)

    If you have other roles as well besides 1 and 2, you can put those into a temp table as well and after the delete from the testcase table in the example above and after the insert for the 1 and 2 role, you can insert the rest in.

    Hth

Viewing 8 posts - 1 through 7 (of 7 total)

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