May 15, 2008 at 12:51 am
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. 🙁
May 15, 2008 at 2:16 am
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]
June 30, 2008 at 12:48 am
Hi,
Thanks for your help. I've used your idea.
Rgds,
PL.Seenivasan
Hasan Mansur (5/15/2008)
Here is the solution you wantCreate 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