Remove Similar - 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

General SQL Server Forums

New to SQL Server Programming

Remove Similar

Author

Topic

mattmac
Starting Member

3 Posts

Posted - 2010-04-27 : 09:28:08

Hi Guys,
I need to remove similar records in a table.

They have unique keys (i want to keep the lowest one) after matching up another field. for example

1 a
2 c
3 a
4 g
5 a

I want to delete records 3 & 5 and keep 1.

I also want to use the unique keys which i will delete to remove records from another table that contain those keys.

I'm not very good with SQL yet but I'm learning. Could do with a hand with this though.

Thanks in advance and i hope i was clear enough

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-04-27 : 09:34:19

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS recID
FROM Table1
) AS f
WHERE recID > 1

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 09:35:21

Start with this

select min(col1) as col, col2 from table
group by col2

Madhivanan

Failing to plan is Planning to fail

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 09:56:17

Use Peso's solution, but use "PARTITION BY Col2" rather than "PARTITION BY Col1".

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 10:00:32

quote:
Originally posted by DBA in the making

Use Peso's solution, but use "PARTITION BY Col2" rather than "PARTITION BY Col1".

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Yes. Also we may not know which version of SQL Server OP is using

Madhivanan

Failing to plan is Planning to fail

mattmac
Starting Member

3 Posts

Posted - 2010-04-27 : 10:06:09

thanks for the replies.

I'm getting this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_Found_Hosts". The conflict occurred in database "db_curr", table "dbo.FoundP", column 'InstanceID'.

InstanceID is the key.

I want to delete all but the minimuum records in dbo.FoundP as well.

Do I do something similar to that table first or ...?

I'm using sql server 2005

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 10:55:02

quote:
Originally posted by mattmac
Do I do something similar to that table first or ...?

You can either use a modified version of the supplied query to identify the records you need to delete from the table "dbo.FoundP", and then delete them, OR, you can set the FK constraint to CASCADE ON DELETE, and they'll be deleted automatically when the supplied DELETE query runs.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

mattmac
Starting Member

3 Posts

Posted - 2010-04-27 : 10:59:09

the cascade on delete option sounds good.

how do i use it with:

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col2 ORDER BY Col1) AS recID
FROM Table1
) AS f
WHERE recID > 1

?

cheers

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 11:07:02

Just turn it on. When you run the delete query, the related records will also be deleted. Don't forget to reset the setting to its original value when you're done.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-27 : 16:23:23

quote:
Originally posted by madhivanan

quote:
Originally posted by DBA in the making

Use Peso's solution, but use "PARTITION BY Col2" rather than "PARTITION BY Col1".

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Yes. Also we may not know which version of SQL Server OP is using

Madhivanan

Failing to plan is Planning to fail

This method will work at any version.

DELETE FROM T1
FROM table_name T1
WHERE NOT EXISTS
(SELECT *
FROM table_name T2
GROUP BY T2.col2
HAVING MIN(T2.col1) = T1.col1
AND T2.col2 = T1.Col2);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources