duplicate delete help - 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 2008 Forums

Transact-SQL (2008)

duplicate delete help

Author

Topic

learntsql

524 Posts

Posted - 2011-02-11 : 04:30:29

Hi All,
I have sample data like

ID-Val1-Val2-Status
1-1-1-s
2-1-1-null
3-2-2-s
4-2-2-null
.
.
.
and so on..
and i have to delete duplicate record either
1-1-1-s
OR
2-1-1-null
by keeping other.
TIA.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-11 : 04:33:43

[code]
delete t
from (
select row_no = row_number() over (partition by val1, val2 order by ID)
from yourtable
) t
where t.row_no <> 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

learntsql

524 Posts

Posted - 2011-02-11 : 04:39:40

quote:
Originally posted by learntsql

Hi All,
I have sample data like

ID-Val1-Val2-Status
1-1-1-s
2-1-1-null
3-2-2-s
4-2-2-null
.
.
.
and so on..
and i have to delete duplicate record either
1-1-1-s
OR
2-1-1-null
by keeping other.
TIA.

Sorry guys......i sent wrong sample data
Val1 and val2 are different

ID-Val1-Val2-Status
1-1-10-s
2-1-10-null
3-2-20-s
4-2-20-null
.
.
.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-11 : 04:43:36

so the "duplicate" criteria is based on which column ?

if it is val1 & val2, my query still hold

KH
[spoiler]Time is always against us[/spoiler]

learntsql

524 Posts

Posted - 2011-02-11 : 05:27:30

ThanQ.
Its worked...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources