Delete where img = img

[Oni]

Hi

Im trying to delete duplicate values from a table (img) which has columns img,kod

Never done it before so I tried--

DELETE FROM img WHERE COUNT(img) > 1 GROUP BY imga;
&
ALTER IGNORE TABLE img ADD UNIQUE INDEX(img,img);

What to do? 😕

[bradgrafelman]

I would say create a temporary table, populate it with unique entries of 'img, wipe the old table clean, and then re-insert the non-duplicated data. You'd execute queries like this:

CREATE TEMPORARY TABLE `img_temp`
SELECT * FROM `img` GROUP BY `img`;

DELETE FROM `img`;

INSERT INTO `img` SELECT * FROM `img_temp`;

Though, you're right, an ALTER IGNORE TABLE syntax would work as well:

ALTER IGNORE TABLE `img` ADD UNIQUE INDEX `img` (`img`);

[Oni]

The former worked, for the latter I got a error regarding the blob row type.

[bradgrafelman]

Without seeing the error message, I can't help you debug it. So, if you're satisfied with the first method, then don't forget to mark this thread resolved. Otherwise, you'll have to provide us with the error message (unless someone else knows what's wrong - I'm not DB guru).