How to make a sql query for pruning chars

[msanders1020]

I am trying to figure how to delete in active chars from char db.  I ran this script and it would always time out due to databases being so big

CREATE TEMPORARY TABLE TmpTable AS (SELECT `account_id`,`char_id` FROM `char` WHERE `account_id` IN (SELECT `account_id` FROM `login` WHERE `lastlogin` < (CURRENT_TIMESTAMP - interval '5' month)));

#Add tables to delete from here.
DELETE FROM `guild` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `skill` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `quest` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `mercenary_owner` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `auction` WHERE `seller_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `mercenary` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `memo` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `inventory` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `hotkey` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `cart_inventory` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `homunculus` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `friends` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `global_reg_value` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `elemental` WHERE `char_id` IN (SELECT `char_id` FROM TmpTable);
DELETE FROM `pet` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `party` WHERE `leader_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `guild_member` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `sc_data` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `storage` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `char` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `login` WHERE `account_id` IN (SELECT `account_id` FROM TmpTable);
DELETE FROM `login` WHERE `lastlogin` < (CURRENT_TIMESTAMP - interval '19' month);

DROP TABLE TmpTable;

Then I ran this one and it worked for the login but Not thinking that I need to delete everything that goes with those accounts.  Is there a way to do it with a query or do I have to do it manually now?

DELETE FROM `login` WHERE `lastlogin` < (CURRENT_TIMESTAMP - interval '20' month)

 

 

Thanks in advanced.  

Edited August 11, 2013 by msanders1020