Hello, im erchache from EL (enciclopedia.us.es).
I want to update from 1.3.11 to 1.4.x but i have duplicate entries on image table, and program doesnt finish update process.
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.
+-----------------------------------------------------+-----------------+ | img_name | count(img_name) | +-----------------------------------------------------+-----------------+ | Ac.artemisephesus.jpg | 2 |
This is a example. i want to delete all duplicate entries and set it to 1.
Any can help me? I want to make it with sql sentences to automate process.
Im waiting for your replies.
Thanks.
______________________________________________ Renovamos el Correo Yahoo! Nuevos servicios, más seguridad http://correo.yahoo.es
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
wikitech-l@lists.wikimedia.org