delete duplicate data

[ynotlim]

hello...

I have a table with lots of duplicate data and need to delete them.

Say I have a table with columns fname, lname, process
('Tony','B','2)
('Tony','B','0)
('Tom','L','0)
('Tom','L','0)
('Eric','T','1)
('Eric','T,'0)

I want to delete the duplicate data. And if the process greater than 0, then make sure it deletes the one that is at process 0.

Thanks!

[Fumigator]

Join the table on itself or a subquery to delete rows that match.


DELETE FROM table1 as t1
WHERE EXISTS
(SELECT 1 FROM table1 as t2
WHERE t1.last_name = t2.last_name
AND t1.first_name = t2.first_name
AND t1.unique_primary_key <> t2.unique_primary_key
AND (t1.process_code = '0' OR t1.process_code < t2.process_code))


You'll want to try this on a test table (or back up the table) as I didn't test it and it may not work exactly right (especially the process_code part).

[ynotlim]

thanks for the response.

What if the process code is either a Y, N or null. And I want to keep the one that is not null.

[Fumigator]

Modify your WHERE clause as needed.

[RichestBoy]

Thanks Fumigator,

I was seeking same solution. I learnt new concept in SQL.

Thanks Again