Delete duplicate records from mysql table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ashik pasha
Forum Newbie
Posts: 9
Joined: Mon Dec 01, 2008 9:55 pm

Delete duplicate records from mysql table

Post by ashik pasha »

Hi guys,

I have a problem with deleting duplicate records from mysql table, mysql version is above 5

DELETE FROM `tbl_video_category` WHERE category_id NOT IN (
SELECT MAX( category_id )
FROM `tbl_video_category`
GROUP BY category_name )


Error message showing that 'You can't specify target table 'tbl_video_category' for update in FROM clause'

Any help would be appreciated

Thanks.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Delete duplicate records from mysql table

Post by requinix »

Try a different query:

Code: Select all

DELETE FROM table ORDER BY field DESC LIMIT 1
I think that should work. Otherwise

Code: Select all

DELETE FROM table WHERE primarykey = (SELECT primarykey FROM table ORDER BY field DESC LIMIT 1)
ashik pasha
Forum Newbie
Posts: 9
Joined: Mon Dec 01, 2008 9:55 pm

Re: Delete duplicate records from mysql table

Post by ashik pasha »

The same error repeating again ....why this error showing when mysql support sub queries.. have you any idea..? Inner query working well...

If this query wont work correctly ... can you give me a solution for deleting multiple records from table(cat_id,cat_name)
Post Reply