Having a bit of trouble deleting 8645 duplicate rows...
#//mySQL is broken and you can't reference a table you're deleting from in a
subselect.
#//http://www.thescripts.com/forum/thread490831.html
#// you can't even update said table, so this elegant solution fails too...
#// update buglog set BID = 0 where LogID
#// IN (select LogID from buglog group by BID, TS having count(*) > 1
order by BID);
#// delete from buglog where BID = 0;
So then I tried this hack-method:
DROP TABLE IF EXISTS `dupes`;
CREATE TEMPORARY TABLE dupes
SELECT LogID FROM buglog GROUP BY BID, TS HAVING count(*) > 1 ORDER
BY BID;
LOCK TABLES buglog WRITE;
SELECT * FROM buglog WHERE LogID IN (SELECT LogID FROM dupes) LIMIT 10;
#DELETE FROM buglog WHERE LogID IN (SELECT LogID FROM dupes);
UNLOCK TABLES;
The problem is the SELECT (DELETE) is either taking way too long to return
or it's hung. I don't sit there long enough to figure it out. It seems like
it shouldn't take as long as I wait. If I run the delete version, my buglog
table count never decreases in the time I wait.
mysql> select count(*) from buglog;
+----------+
| count(*) |
+----------+
| 34867 |
+----------+
mysql> select count(*) from dupes;
+----------+
| count(*) |
+----------+
| 8645 |
+----------+
What am I doing wrong?
Is there a better way to delete the duplicate rows?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
