Compare rows and delete if some records match..

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)

Compare rows and delete if some records match..

Author

Topic

Mike_P
Starting Member

2 Posts

Posted - 2011-06-13 : 12:20:53

Hello,
I am looking for a query that would find four guys (out of 5 possible) in two rows and if it finds that it should delete one row. This should keep going from first row to the last one in the table. Our results should be rows 1, 6 and 15.
We are getting results 1,6,15,20 and 21 which is not right..
See what we've got so far..

INSERT INTO tblTEST(ID, FirstColumn,SecondColumn,ThirdColumn,FourthColumn, FifthColumn)
values
('1','Alex','George','Gerard','Michael','Paul'),
('2','Alex','George','Gerard','Michael','Peter'),
('3','Alex','George','Gerard','Michael','Thomas'),
('4','Alex','George','Gerard','Paul','Peter'),
('5','Alex','George','Gerard','Paul','Thomas'),
('6','Alex','George','Gerard','Peter','Thomas'),
('7','Alex','George','Michael','Paul','Peter'),
('8','Alex','George','Michael','Paul','Thomas'),
('9','Alex','George','Michael','Peter','Thomas'),
('10','Alex','George','Paul','Peter','Thomas'),
('11','Alex','Gerard','Michael','Paul','Peter'),
('12','Alex','Gerard','Michael','Paul','Thomas'),
('13','Alex','Gerard','Michael','Peter','Thomas'),
('14','Alex','Gerard','Paul','Peter','Thomas'),
('15','Alex','Michael','Paul','Peter','Thomas'),
('16','George','Gerard','Michael','Paul','Peter'),
('17','George','Gerard','Michael','Paul','Thomas'),
('18','George','Gerard','Michael','Peter','Thomas'),
('19','George','Gerard','Paul','Peter','Thomas'),
('20','George','Michael','Paul','Peter','Thomas'),
('21','Gerard','Michael','Paul','Peter','Thomas')

declare @DupLoop int

set @DupLoop = 1
while @DupLoop = 1
begin
;with Dups as (SELECT DISTINCT a.ID AS DuplicateID
FROM dbo.tblTEST AS t
LEFT JOIN dbo.tblTEST AS a ON a.ID > t.ID
AND a.FirstColumn IN (t.FirstColumn, t.SecondColumn, t.ThirdColumn, t.FourthColumn, t.FifthColumn)
LEFT JOIN dbo.tblTEST AS b ON b.ID = a.ID
AND b.SecondColumn IN (t.FirstColumn, t.SecondColumn, t.ThirdColumn, t.FourthColumn, t.FifthColumn)
LEFT JOIN dbo.tblTEST AS c ON c.ID = a.ID
AND c.ThirdColumn IN (t.FirstColumn, t.SecondColumn, t.ThirdColumn, t.FourthColumn, t.FifthColumn)
LEFT JOIN dbo.tblTEST AS d ON d.ID = a.ID
AND d.FourthColumn IN (t.FirstColumn, t.SecondColumn, t.ThirdColumn, t.FourthColumn, t.FifthColumn)
LEFT JOIN dbo.tblTEST AS e ON e.ID = a.ID
AND e.FifthColumn IN (t.FirstColumn, t.SecondColumn, t.ThirdColumn, t.FourthColumn, t.FifthColumn)
WHERE CASE WHEN a.ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN b.ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN c.ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN d.ID IS NULL THEN 0 ELSE 1 END
+ CASE WHEN e.ID IS NULL THEN 0 ELSE 1 END >= 4)

delete from tblTEST where ID in (select top (1) DuplicateID from Dups ORDER BY DuplicateID)
select @DupLoop = @@ROWCOUNT
end

select * from tblTest

Any help will be appreciated.
Mike.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-13 : 19:30:05

It's not clear to me what the expected output would be.

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-06-13 : 23:13:24

1. which four?
2. Why do you say 1,6 and 15 because I do not see the four you want to keep?

If you don't have the passion to help people, you have no passion

Mike_P
Starting Member

2 Posts

Posted - 2011-06-14 : 05:24:49

What I am trying to do is,,
we're not going to delete row number 1. This is the first record in the table. Row 2 will be deleted because four guys from this record (Alex, George, Gerard and Michael) can be found in find in row 1. Row 3 gets deleted as well because of the same reason. Row four gets deleted because 4 guys (Alex, George, Gerard and Paul this time) are matched in row 1. Row 5 deleted because of the same reason as row 4. Now, if you get to the row #6 there are only three guys matching (Alex, George, Gerard) while comparing to the row 1. We are not comparing to the row 2 because that one was deleted. So we know two record are not being deleted now. They are rows 1 and 6. We keep going till the end. Now we compare row 7 to these two rows 1 and 6. I hope this makes sense...
Thanks.
M

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-06-14 : 13:24:24

A better solution would be NORMALIZING your tables.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources