[RESOLVED] Removing duplicate rows, leaving one?

[mrbaseball34]

Got this table:

CREATE TABLE `ps_image` (
        `id_image`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_product` INT(10) UNSIGNED NOT NULL,
        `position`   TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
        `cover`      TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
        PRIMARY KEY (`id_image`),
        INDEX `image_product` (`id_product`),
        INDEX `product_position` (`id_product`, `position`),
        INDEX `id_product_cover` (`id_product`, `cover`)
)

I am getting duplicate id_product and need to remove them. What SQL would delete all rows with duplicate id_product from this table but leave one for each id_product?

ALL records have same position and cover values.

What would you suggest for indexes?

[mrbaseball34]

Tried this but it doesn't work, it inserts all duplicate rows, too.

DROP TABLE IF EXISTS `new_ps_image`;
CREATE TABLE `new_ps_image` (
        `id_image`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        `id_product` INT(10) UNSIGNED NOT NULL,
        `position`   TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
        `cover`      TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
        PRIMARY KEY (`id_image`),
        INDEX `image_product` (`id_product`, `position`, `cover`),
        INDEX `product_position` (`id_product`, `position`),
        INDEX `id_product_cover` (`id_product`, `cover`)
)
ENGINE=InnoDB
ROW_FORMAT=DEFAULT;

INSERT INTO new_ps_image SELECT * FROM ps_image 

[bradgrafelman]

Add a UNIQUE index instead of just a regular INDEX.

[mrbaseball34]

Here's the solution to get rid of them all but one:

DELETE t1
FROM ps_image_lang t1, ps_image_lang t2
WHERE t1.id_product = t2.id_
AND t1.id_image > t2.id_image

I will fix the index, just use the unique keyword, huh?