Deleting identical rows question

[WebmasterA]

Hello,

I have a problem.
I have a table with identical row information.
I need to delete duplicates.
For exaple I have a table my_posts with data:
post_id post
1 first post
2 second post
2 second post
3 third post

I need it to be:
post_id post
1 first post
2 second post
3 third post

I cannot find the way to distinguish the rows.
Plase assist.

Thank you.

 

[aardvark92]

This is probably a convoluted solution, but here's my idea:

Copy the post field from "second post" to a new row; assign it post_id 9999 or something. Then delete all rows with post_id = 2. Then update the post_id from 9999 to 2.

Then make post_id a primary key so MySQL will not accept duplicate values ever again.

 

[WebmasterA]

Thank for the reply. I may work but I have like 10000 records.
I just found an answer.
I tried it and it works.

DELETE FROM `my_posts` WHERE `post_id`=2 LIMIT 1;

Good luck to everyone who has same problem.
I found like 5 similar posts on this board but no similar and simple answer.

- Webmaster