no lssi no lssi wrote:
With this sentence i can see all duplicated entries:
SELECT img_name FROM image GROUP BY img_name HAVING
count(img_name) > 1;
But now i cant do a delete with a subquery to erase
duplicate entries less one.
The problem, of course, is that if you try to delete "where img_name =
'some_name'", then you will remove both duplicates.
I have come across this problem before, at work where we use Microsoft
SQL Server. Here I can add a new column to the table, define that
column to be an auto-incrementing primary key, and then use these
"temporary IDs" to delete all of the duplicates except one.
DELETE FROM img_name WHERE temporary_id IN
(SELECT i2.temporary_id FROM img_name i1, img_name i2
WHERE i1.temporary_id < i2.temporary_id
AND i1.img_name = i2.img_name
)
or something like that.
After the deletion, you can simply remove that temporary_id column again.
Timwi