Update Duplicate Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Duplicate Records

Author

Topic

maximgor
Starting Member

7 Posts

Posted - 2009-04-14 : 17:21:59

I have one field (column) called ID1 that has records 1,2,3,4,5,6 and so on. I have another field ID2 that has also records 1,1,1,2,2,1 and so on. They are not related. I also have a field called amount($) 100, 100, 100, 200, 200, 100. So, it could make raws such as 1 1 100 and 2 1 100 and 3 1 100. I need to create SQL statement that will create another amount (amount2) field that will show 0 for duplicate records. The duplicate records are the ones that have the same ID2 and amount records. For example, if raws look like that: 1(ID1) 1(ID2) 100(amount) and 2 1 100 and 7 1 100. I need to have 2 1 100 0 and 7 1 100 0. Thank you for your help!

MG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-04-15 : 05:00:09

moved from script library.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

matty
Posting Yak Master

161 Posts

Posted - 2009-04-15 : 05:50:40

DECLARE @t TABLE
(
Id1 int identity(1,1),
Id2 int,
Amount money
)

insert @t(Id2,Amount)
select 1,100
union all
select 1,100
union all
select 1,100
union all
select 2,200
union all
select 2,200
union all
select 1,100

SELECT Id1,Id2,Amount,0 AS Amount2
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Id2,Amount ORDER BY Id1) AS Rownum
FROM @t
)a
WHERE Rownum > 1

maximgor
Starting Member

7 Posts

Posted - 2009-04-15 : 08:45:15

Thank you for your help! Although, this didn't work. I already have the table with 4 columns. ID1, ID2, amount, amount2 (no records).I need some sql statement(s) that will insert the value of amount in amount2. In case, if there is duplicate record (ID2 value and amount value are repeated), then it will insert '0' in amount2.

<<

quote:
Originally posted by matty

DECLARE @t TABLE
(
Id1 int identity(1,1),
Id2 int,
Amount money
)

insert @t(Id2,Amount)
select 1,100
union all
select 1,100
union all
select 1,100
union all
select 2,200
union all
select 2,200
union all
select 1,100

SELECT Id1,Id2,Amount,0 AS Amount2
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY Id2,Amount ORDER BY Id1) AS Rownum
FROM @t
)a
WHERE Rownum > 1

>>

MG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 14:11:25

[code]
UPDATE t
SET t.amount2=CASE WHEN t.Seq>1 THEN 0 ELSE t.amount END
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ID2,Amount ORDER BY ID) AS Seq,Amount,Amount2
FROM YourTable
)t
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources