assigning zeros to duplicate rows

  • hi,

    I'm having a temp table like below

    code1 description amt

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

    S123 onetwothree 15

    S123 onetwothree 15

    In the above table i want the amt field of the duplicate row to show/have 0 as below

    code description amt

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

    S123 onetwothree 15

    S123 onetwothree 0

    Is it possible ? Can any one plz help me on this. 🙁

  • Here is the solution you want

    Create table #new(id int null, Keyvalue varchar(2),amt int)

    insert into #new(id,keyvalue,amt) values (1,'aa',11)

    insert into #new(id,keyvalue,amt) values (2,'bb',22)

    insert into #new(id,keyvalue,amt) values (1,'aa',11)

    insert into #new(id,keyvalue,amt) values (1,'aa',11)

    with numbered as(

    SELECT rowno=row_number() over (partition by id order by id),id,keyvalue,amt from #new)

    UPDATE numbered

    SET amt = 0

    where rowno>1

    SELECT * FROM [#new]

  • Hi,

    Thanks for your help. I've used your idea.

    Rgds,

    PL.Seenivasan

    Hasan Mansur (5/15/2008)


    Here is the solution you want

    Create table #new(id int null, Keyvalue varchar(2),amt int)

    insert into #new(id,keyvalue,amt) values (1,'aa',11)

    insert into #new(id,keyvalue,amt) values (2,'bb',22)

    insert into #new(id,keyvalue,amt) values (1,'aa',11)

    insert into #new(id,keyvalue,amt) values (1,'aa',11)

    with numbered as(

    SELECT rowno=row_number() over (partition by id order by id),id,keyvalue,amt from #new)

    UPDATE numbered

    SET amt = 0

    where rowno>1

    SELECT * FROM [#new]

Viewing 3 posts - 1 through 2 (of 2 total)

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