CODEWITHSUNDEEP
phpmysqlsqlsql-delete
Manos Serifios
Manos Serifios

Reputation: 577

MySQL delete duplicate rows except NULL

I have a table (Final) in my db and i want to delete all the duplicate rows except
those who have a NULL value;
I tried the above :

 DELETE FROM Final 
  WHERE event_id NOT IN (SELECT MIN(event_id) AS MinID 
                           FROM (SELECT * FROM Final) as x 
                          GROUP BY event_name
                            AND event_name IS NOT NULL)

but didn't work.

edit

my table seems like:

id | Coral
1 | NULL
2 | 1
3 | 1 <- DELETE
4 | 2
5 | NULL <- STAY as it is
6 | 1 <-DELETE

Upvotes: 0

Views: 178

Answers (2)

Andriy M
Andriy M

Reputation: 77707

Just move the AND event_name IS NOT NULL part to the main subquery:

DELETE FROM Final 
  WHERE event_id NOT IN (SELECT MIN(event_id) AS MinID 
                           FROM (SELECT * FROM Final) as x 
                          GROUP BY event_name)
    AND event_name IS NOT NULL

Upvotes: 1

John Woo
John Woo

Reputation: 263803

how about joining the tables?

DELETE  a
FROM    Final a
        LEFT JOIN
        (
            SELECT  Event_name, MIN(Event_ID) MIN_ID
            FROM    Final
            WHERE   Event_name IS NOT NULL
            GROUP   BY Event_name
        ) b ON a.Event_name = b.Event_name AND
                a.Event_ID = b.MIN_ID
WHERE   a.Event_name IS NOT NULL AND 
        b.Event_name IS NULL

Upvotes: 1

Related Questions