Delete Duplicate - 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)

Delete Duplicate

Author

Topic

MaveriK
Starting Member

7 Posts

Posted - 2015-03-10 : 03:38:06

Hi,

I need to delete a duplicate key from a table.
eg:
ID1 and ID2 are composite keys and description ending with ".A" is the active version which should remain while the old one is deleted.

SELECT * FROM Test_Table;

ID1 ID2 DESC
1 A ZZZ
1 A AAA.A
1 B XXX
2 A DDD
2 B CCC

Only the first record(1,'A','ZZZ') should be deleted and not (1,'A','AAA.A').

Can someone please give the SQL for this logic.
TIA.

Thanks & Regards
MaveriK

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-03-10 : 04:01:36

[code]
;WITH ACTE
AS
(
SELECT *
,ROW_NUMBER() OVER(PARTITION BY ID1,ID2 ORDER BY RIGHT([DESC],1) ASC) AS RN
FROM tabla_1
)
DELETE ACTE
WHERE RN > 1

SELECT * FROM tabla_1
[/code]

sabinWeb MCP

MaveriK
Starting Member

7 Posts

Posted - 2015-03-10 : 10:31:19

Thank YOu

Thanks & Regards
MaveriK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources