Support:Database Cleanup: Difference between revisions
Content deleted Content added
imported>Kymara |
imported>Hendrik Brummermann |
||
| (46 intermediate revisions by 2 users not shown) | |||
Line 58:
DELETE FROM del2 WHERE EXISTS (SELECT null FROM gameEvents_2009_08_17 WHERE charname=source);
DELETE FROM del2 WHERE EXISTS (SELECT null FROM itemlog WHERE charname = source);
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 c3.charname FROM characters As c3,
(SELECT account.id As account_id FROM account, del2, characters WHERE account.username=del2.charname AND account.id=player_id
AND NOT EXISTS (SELECT null FROM del2 As t WHERE t.charname = characters.charname)
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;
DROP TABLE del3;
</source>
=== Delete ===
<source lang="sql">
DELETE FROM reached_achievement USING reached_achievement, del2 WHERE reached_achievement.charname = del2.charname;
DELETE FROM postman USING postman, del2 WHERE source = del2.charname;
DELETE FROM postman USING postman, del2 WHERE target = del2.charname;
DELETE FROM buddy USING buddy, del2 WHERE buddy.charname = del2.charname;
DELETE FROM buddy USING buddy, del2 WHERE buddy.buddy = del2.charname;
DELETE FROM character_stats USING character_stats, del2 WHERE name = del2.charname;
UPDATE halloffame, del2 SET halloffame.charname = concat('del.2012-05-06.', halloffame.charname) WHERE del2.charname=halloffame.charname;
UPDATE halloffame_archive_alltimes, del2 SET halloffame_archive_alltimes.charname = concat('del.2012-05-06.', halloffame_archive_alltimes.charname) WHERE del2.charname=halloffame_archive_alltimes.charname;
UPDATE halloffame_archive_recent, del2 SET halloffame_archive_recent.charname = concat('del.2012-05-06.', halloffame_archive_recent.charname) WHERE del2.charname=halloffame_archive_recent.charname;
UPDATE halloffame_archive, del2 SET halloffame_archive.charname = concat('del.2012-05-06.', halloffame_archive.charname) WHERE del2.charname=halloffame_archive.charname;
UPDATE cid, del2 SET cid.charname = concat('del.2012-05-06.', cid.charname) WHERE del2.charname=cid.charname;
-- TODO account
UPDATE characters, del2 SET characters.status='deleted' AND characters.charname = concat('del.2012-05-06.', characters.charname) WHERE del2.charname=characters.charname;
</source>
| |||