Support:Database Cleanup: Difference between revisions

← Older edit Newer edit →

Content deleted Content added

Inline

@@ Line 60: / Line 60: @@
 DELETE FROM del2 WHERE EXISTS (SELECT null FROM kills WHERE kills.killed = charname AND killed_type='P');
 DELETE FROM del2 WHERE EXISTS (SELECT null FROM kills WHERE kills.killer = charname AND killer_type='P');
+CREATE TABLE del3 (charname VARCHAR(50), PRIMARY KEY(charname));
 -- check
+INSERT INTO del3
-SELECT username, count(*) FROM account, del2, characters WHERE account.username=del2.charname AND account.id=player_id
+SELECT c3.charname FROM characters As c3,
-AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = characters.charname)
+    (SELECT account.id As account_id FROM account, del2, characters WHERE account.username=del2.charname AND account.id=player_id
-GROUP BY username
+    AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = characters.charname)
-HAVING count(*) > 1;
+    GROUP BY username
+    HAVING count(*) > 1) As x
+WHERE x.account_id = c3.player_id;
+INSERT INTO del3
+SELECT c3.charname FROM characters As c3,
+    (SELECT account.id As account_id, count(*)
+     FROM account, del2, characters As c1, characters As c2
+     WHERE account.id=c1.player_id AND c1.charname=del2.charname AND account.id=c2.player_id
+     AND NOT EXISTS (
+          SELECT null FROM del2 As t WHERE t.charname = c2.charname)
+     GROUP BY account_id
+     HAVING count(*) > 1) As x
+WHERE x.account_id = c3.player_id;
+DELETE FROM del2 USING del2, del3 WHERE del2.charname = del3.charname;
-SELECT username, count(*) FROM account, del2, characters As c1, characters As c2
+DROP TABLE del3;
-WHERE account.id=c1.player_id AND c1.charname=del2.charname AND account.id=c2.player_id
-AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = c2.charname)
-GROUP BY username
-HAVING count(*) > 1;
 </source>

Support:Database Cleanup: Difference between revisions

Navigation menu

Search