Jump to content

[Share]Cleaning db


Recommended Posts

SET @dias = 1;

DELETE FROM accounts WHERE DATEDIFF( CURRENT_DATE( ) , FROM_UNIXTIME( `lastactive` /1000 ) ) > @dias;
DELETE FROM accounts WHERE login NOT IN (SELECT account_name FROM characters);
DELETE FROM account_data WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM characters WHERE account_name NOT IN (SELECT login FROM accounts);
DELETE FROM character_friends WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_hennas WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_macroses WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_quest_global_data WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_quests WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_raid_points WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_recipebook WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_recommends WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_shortcuts WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_skills WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_skills_save WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_subclasses WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM character_tpbookmark WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM clan_data WHERE leader_id NOT IN (SELECT charId FROM characters);
DELETE FROM clan_notices WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_privs WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_skills WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM items WHERE owner_id NOT IN (SELECT charId FROM characters) AND owner_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM pets WHERE item_obj_id NOT IN (SELECT object_id FROM items);
DELETE FROM seven_signs WHERE charId NOT IN (SELECT charId FROM characters);
UPDATE characters SET clanid=0,title="",clan_privs=0,lvl_joined_academy=0,apprentice=0,sponsor=0 where clanid NOT IN (SELECT clan_id FROM clan_data);
UPDATE clanhall SET ownerID=0,paidUntil=0,paid=0 where ownerID NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clanhall_functions WHERE hall_id NOT IN (SELECT id FROM clanhall WHERE ownerId=0);
DELETE FROM clan_subpledges WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM clan_wars WHERE clan1 NOT IN (SELECT clan_id FROM clan_data) OR clan2 NOT IN (SELECT clan_id FROM clan_data);
UPDATE castle SET taxPercent="0",treasury="0" WHERE id NOT IN (SELECT hasCastle FROM clan_data where hasCastle>0);
DELETE FROM castle_doorupgrade WHERE doorId IN (SELECT id FROM castle_door where castleId NOT IN (SELECT hasCastle FROM clan_data where hasCastle>0));
DELETE FROM castle_functions WHERE castle_id NOT IN (SELECT hasCastle FROM clan_data WHERE hasCastle>0);
DELETE FROM castle_manor_procure WHERE castle_id NOT IN (SELECT hasCastle FROM clan_data WHERE hasCastle>0);
DELETE FROM castle_manor_production WHERE castle_id NOT IN (SELECT hasCastle FROM clan_data WHERE hasCastle>0);
DELETE FROM siege_clans WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
UPDATE siege_clans SET castle_owner="" WHERE castle_owner NOT IN (SELECT clan_id FROM clan_data);
UPDATE fort SET owner=0,castleId=0 WHERE owner NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM fort_doorupgrade WHERE fortId IN (SELECT id FROM fort WHERE owner=0);
DELETE FROM fort_functions WHERE fort_Id IN (SELECT id FROM fort WHERE owner=0);
UPDATE fort_spawnlist SET castleId=0 WHERE castleId NOT IN (SELECT hasCastle FROM clan_data where hasCastle>0);
DELETE FROM fortsiege_clans WHERE clan_id NOT IN (SELECT clan_id FROM clan_data);
DELETE FROM heroes WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM olympiad_nobles WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM olympiad_nobles_eom WHERE charId NOT IN (SELECT charId FROM characters);
DELETE FROM mods_wedding WHERE player1Id NOT IN (SELECT charId FROM characters) OR player2Id NOT IN (SELECT charId FROM characters);

Link to comment
Share on other sites

Guest
This topic is now closed to further replies.
 Share



×
×
  • Create New...

AdBlock Extension Detected!

Our website is made possible by displaying online advertisements to our members.

Please disable AdBlock browser extension first, to be able to use our community.

I've Disabled AdBlock