Site Trick

I always encounter database tables with duplicate data. I used to create a function using PHP to compare each row with the rest of the table entries but recently I've been using this SQL query:

delete from books
USING books, books as vtable
WHERE (books.id > vtable.id)
AND (books.title=vtable.title)
AND (books.author=vtable.author)


1. Here you tell mysql that there is a table named "books".
2. Then you tell it that you will use "books" and a virtual table with the values of "books".
3. The third line will prevent mysql from comparing a record with itself and tells it to keep the record with lowest ID number, use < to keep the record with the highest ID number.
4. In the fourth line you tell mysql that there shouldn’t be records with the same title.
5. The fifth line is an optional comparison to compare an additional column, the author column.