Page 1 of 1

Delete duplicate records from mysql table

Posted: Mon Dec 01, 2008 10:03 pm
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.

Re: Delete duplicate records from mysql table

Posted: Tue Dec 02, 2008 12:55 am
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)

Re: Delete duplicate records from mysql table

Posted: Tue Dec 02, 2008 4:28 am
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)