Support:Database Cleanup: Difference between revisions
Content deleted Content added
imported>Hendrik Brummermann |
imported>Hendrik Brummermann |
||
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)▼
▲
GROUP BY username▼
▲ AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = characters.charname)
HAVING count(*) > 1;▼
▲ GROUP BY username
WHERE x.account_id = c3.player_id;
INSERT INTO del3
SELECT c3.charname FROM characters As c3,
(SELECT account.id As account_id, count(*)
WHERE account.id=c1.player_id AND c1.charname=del2.charname AND account.id=c2.player_id▼
AND NOT EXISTS (
GROUP BY account_id
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)
▲HAVING count(*) > 1;
</source>
| |||