script to fish out dups and delete

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

script to fish out dups and delete

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-12 : 11:49:46

so, what i 'think' i need, is to be able to create a script which will look at all columns and find entries which match for all columns, and leave only one behind in the case of such matches. There are two fields for the script to look at which will likely not match. These are pseqno, and set_amount. In the event that ALL other fields match, but the pseqno and set_amount contain different data than the other matching rows, i need to leave those intact.

what would such an abomination look like?

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 11:53:39

Hard to figure out what you really want without details

This shows all unique rows

SELECT Col1, Col2, Col3, COUNT(*) FROM (
SELECT Col1, Col2, Col3
FROM Table1
UNION ALL
SELECT Col1, Col2, Col3
FROM Table2
) AS XXX
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) = 1

Change to > 1 to see all the duplicates

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-12 : 12:02:30

I think this is a more refined version of what i actually want, but i'm unable to change it from a DELETE to a SELECT so i can test it.

Delete D FROM
(
SELECT Arrestno, pseqno, bondid, set_amount, ROW_NUMBER()OVER(ORDER BY Arrestno) AS RowNum
FROM dbo.BOTHWARRANTFILES)D
JOIN
(
SELECT Arrestno, pseqno, bondid, set_amount, ROW_NUMBER()OVER(ORDER BY Arrestno) AS RowNum
FROM dbo.BOTHWARRANTFILES
)E
on D.arrestno = E.arrestno
AND D.RowNum < E.RowNum
AND D.pseqno=e.pseqno
AND d.bondid=e.bondid
AND d.set_amount=e.set_amount
AND d.set_amount IS NOT NULL

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-01-12 : 12:03:01

by "unable", i mean, when i change "Delete" to "Select", it blows up.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 12:10:35

Let me rephrase

I want to find Duplicates in BOTHWARRANTFILES. I want to keep only 1. I want to discard the one that has ______________(?) and keep the one with _____________ (?)

So I need to identify first, the ones I want to discard, so I can delete them.

How do I do that?

My Table DDL looks like

CREATE TABLE BOTHWARRANTFILES (
Arrestno...., pseqno...
)
GO

My Sample Data Looks like:

INSERT INTO BOTHWARRANTFILES (Arrestno, pseqno, bondid, set_amount)
SELECT data,data,data,data UNION ALL
SELECT data,data,data,data UNION ALL
ect

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources