Often you want to delete rows from a table in mysql. How can you achieve it? Let’s say you have user table which has users with duplicate column (mysql field) value. These data need to be sorted out. Query should be like this:
1 2 3 4 5 6 | DELETE FROM user USING user, user as vtable WHERE (user.id > vtable.id) AND (user.duplicate_field = vtable.duplicate_field); |
So the idea is simple. To remove duplicate rows with same column value, the same table is used as alias. If two row has same duplicate values whether those rows don’t have same primary key or unique key, then those are duplicates. The action is to delete them to get rid of duplicate rows.
It’s a small trick. It can be useful for real life work and also in interview.
