Search code examples
phpmysqlsqlsql-deletedelete-row

delete all non-duplicate rows but keep one

In MySQL, I have a table like so

book | user | phone
445    a       69
445    g       75
445    e       22
445    l       55
332    w       57
332    u       34
332    v       87
881    o       49

I want to delete all the rows that contain the book 445 , and book 332 and randomly keep only one of them at the end.

So the table will become

book | user | phone
445    l       55
332    w       57
881    o       49

How do I do this in SQL? I use phyMyAdmin and PHP 5.6

Thanks

Solution

  • Here u go:

    DELETE FROM table1 t1, table1 t2 WHERE t1.user > t2.user AND t1.book = t2.book

    substitute the table1 with your actual table name