• This is a read only backup of the old Emudevs forum. If you want to have anything removed, please message me on Discord: KittyKaev

[SOLVED] Some sql help

Status
Not open for further replies.
Minion

Minion

Enthusiast
SHORT STORY: i run a small private server, my database has gotten out of control and i want to greatly reduce the number of "fake" and "unused" accounts.. Im not a genius when it comes to sql but i do pick up quick.

GOAL: i want to dump accounts not active since `X` date, and all data associated with that account.

Did alot of searching and found this on another thread. so all credits and respect to them.

Code: 
SET @REALMD := 'auth';
SET @CHARACTERS := 'charlk';
SET @DATE := '2012-12-12 00:00:00';
DELETE FROM auth.account WHERE `last_login`< '2012-12-30 00:00:00;
TRUNCATE TABLE `account_data`;
DELETE FROM `arena_team` WHERE captainGuid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `arena_team_member` WHERE arenaTeamId NOT IN (SELECT arenaTeamId FROM `arena_team` );
DELETE FROM `arena_team_member` WHERE guid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `auctionhouse` WHERE itemowner NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_account_data`;
DELETE FROM @CHARACTERS WHERE ( `account` ) NOT IN ( SELECT id FROM @REALMD.`account` );
DELETE FROM `account_instance_times` WHERE ( `accountId` ) NOT IN ( SELECT id FROM @REALMD.`account` );
DELETE FROM `character_action` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement_progress` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_arena_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_aura` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_data` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_random` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_equipmentsets` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_feed_log` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_gifts` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_glyphs` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_homebind` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_instance`;
DELETE FROM `character_inventory` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_pet` WHERE ( owner ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_daily` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_weekly` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_reputation` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_social` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_talent` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_spell` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_skills` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_spell_cooldown`;
DELETE FROM `character_tutorial` WHERE ( account ) NOT IN ( SELECT id FROM @REALMD.`account` );
DELETE FROM `corpse` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `creature_respawn`;
TRUNCATE TABLE `gameobject_respawn`;
TRUNCATE TABLE `gm_tickets`;
TRUNCATE TABLE `groups`;
TRUNCATE TABLE `group_instance`;
TRUNCATE TABLE `group_member`;
DELETE FROM `guild_bank_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_item` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_right` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_tab` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_member` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_rank` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
TRUNCATE TABLE `instance`;
TRUNCATE TABLE `instance_reset`;
DELETE FROM `item_refund_instance` WHERE ( player_guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `item_refund_instance` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `item_soulbound_trade_data` WHERE `itemGuid` NOT IN (SELECT `guid` FROM `item_instance`);
TRUNCATE TABLE `lag_reports`;
DELETE FROM `mail` WHERE (receiver ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `mail_items` WHERE (mail_id ) NOT IN ( SELECT id FROM `mail` );
DELETE FROM `petition` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition` WHERE (petitionguid ) NOT IN ( SELECT guid FROM `item_instance` );
DELETE FROM `petition_sign` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE playerguid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE petitionguid NOT IN (SELECT petitionguid FROM `petition` );
TRUNCATE TABLE `pet_aura`;
DELETE FROM `pet_spell` WHERE guid NOT IN (SELECT id FROM `character_pet` );
TRUNCATE TABLE `pet_spell_cooldown`;
DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`);
DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character_inventory`) AND `owner_guid`<>'0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);

now after reading through it this should accomplish exactly what i want. so i run it.

Problem:
Code: 
Query:

TRUNCATE TABLE `pet_aura`;
DELETE FROM `pet_spell` WHERE guid NOT IN (SELECT id FROM `character_pet` );
TRUNCATE TABLE `pet_spell_cooldown`;
DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`);
DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character_inventory`) AND `owner_guid`<>'0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`); 


Error occured at:2013-10-19 12:47:40
Line no.:77
Error Code: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `i' at line 1

that is the error i get (first half of error log deleted to save space) any ideas?
 
Tommy

Tommy

Founder
Just letting you know, "TRUNCATE TABLE" deletes all of the data in the table.

Anyway, the issue is the query is missing ' at end a datetime string.

Code: 
SET @REALMD := 'auth';
SET @CHARACTERS := 'charlk';
SET @DATE := '2012-12-12 00:00:00';
DELETE FROM auth.account WHERE `last_login`< '2012-12-30 00:00:00';
TRUNCATE TABLE `account_data`;
DELETE FROM `arena_team` WHERE captainGuid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `arena_team_member` WHERE arenaTeamId NOT IN (SELECT arenaTeamId FROM `arena_team` );
DELETE FROM `arena_team_member` WHERE guid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `auctionhouse` WHERE itemowner NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_account_data`;
DELETE FROM @CHARACTERS WHERE ( `account` ) NOT IN ( SELECT id FROM @REALMD.`account` );
DELETE FROM `account_instance_times` WHERE ( `accountId` ) NOT IN ( SELECT id FROM @REALMD.`account` );
DELETE FROM `character_action` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement_progress` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_arena_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_aura` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_data` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_random` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_equipmentsets` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_feed_log` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_gifts` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_glyphs` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_homebind` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_instance`;
DELETE FROM `character_inventory` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_pet` WHERE ( owner ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_daily` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_weekly` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_reputation` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_social` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_talent` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_spell` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_skills` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_spell_cooldown`;
DELETE FROM `character_tutorial` WHERE ( account ) NOT IN ( SELECT id FROM @REALMD.`account` );
DELETE FROM `corpse` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `creature_respawn`;
TRUNCATE TABLE `gameobject_respawn`;
TRUNCATE TABLE `gm_tickets`;
TRUNCATE TABLE `groups`;
TRUNCATE TABLE `group_instance`;
TRUNCATE TABLE `group_member`;
DELETE FROM `guild_bank_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_item` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_right` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_tab` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_member` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_rank` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
TRUNCATE TABLE `instance`;
TRUNCATE TABLE `instance_reset`;
DELETE FROM `item_refund_instance` WHERE ( player_guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `item_refund_instance` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `item_soulbound_trade_data` WHERE `itemGuid` NOT IN (SELECT `guid` FROM `item_instance`);
TRUNCATE TABLE `lag_reports`;
DELETE FROM `mail` WHERE (receiver ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `mail_items` WHERE (mail_id ) NOT IN ( SELECT id FROM `mail` );
DELETE FROM `petition` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition` WHERE (petitionguid ) NOT IN ( SELECT guid FROM `item_instance` );
DELETE FROM `petition_sign` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE playerguid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE petitionguid NOT IN (SELECT petitionguid FROM `petition` );
TRUNCATE TABLE `pet_aura`;
DELETE FROM `pet_spell` WHERE guid NOT IN (SELECT id FROM `character_pet` );
TRUNCATE TABLE `pet_spell_cooldown`;
DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`);
DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character_inventory`) AND `owner_guid`<>'0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);

Fixed now.
 
Minion

Minion

Enthusiast
omfg... im an idiot. <insert facepalm>

thank you for the reply!

And as for the truncate comment. i figured that (not an expert) which is why i was attempting this in a test database. As for the functionality if i remove truncate.. will this function as i believe it should?

also this should work to correct?:

Code: 
SET @REALMD := 'realmd';
SET @CHARACTERS := 'characters';
SET @DATE := '2010-07-06 00:00:00';
DELETE FROM @REALMD.account WHERE `last_login`< @DATE;

and change the date variable where "set @date" is the date i want it to be?
 
Last edited:
Minion

Minion

Enthusiast
it does not.. unfortunately. here are some more outputs. sorry im quite frustrated with this.

Code: 
SET @REALMD := 'auth';
SET @CHARACTERS := 'charlk';
SET @DATE := '2012-12-12 00:00:00';
DELETE FROM @REALMD.account WHERE `last_login`< @DATE;
DELETE FROM @CHARACTERS WHERE ( `account` ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `arena_team` WHERE captainGuid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `arena_team_member` WHERE arenaTeamId NOT IN (SELECT arenaTeamId FROM `arena_team` );
DELETE FROM `arena_team_member` WHERE guid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `auctionhouse` WHERE itemowner NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `account_instance_times` WHERE ( `accountId` ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `character_action` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement_progress` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_arena_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_aura` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_data` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_random` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_equipmentsets` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_feed_log` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_gifts` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_glyphs` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_homebind` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_inventory` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_pet` WHERE ( owner ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_daily` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_weekly` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_reputation` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_social` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_talent` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_spell` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_skills` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_tutorial` WHERE ( account ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `corpse` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_bank_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_item` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_right` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_tab` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_member` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_rank` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `item_refund_instance` WHERE ( player_guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `item_refund_instance` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `item_soulbound_trade_data` WHERE `itemGuid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail` WHERE (receiver ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `mail_items` WHERE (mail_id ) NOT IN ( SELECT id FROM `mail` );
DELETE FROM `petition` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition` WHERE (petitionguid ) NOT IN ( SELECT guid FROM `item_instance` );
DELETE FROM `petition_sign` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE playerguid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE petitionguid NOT IN (SELECT petitionguid FROM `petition` );
DELETE FROM `pet_spell` WHERE guid NOT IN (SELECT id FROM `character_pet` );
DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`);
DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character_inventory`) AND `owner_guid`<>'0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);

this offers up:
Code: 
Query:
DELETE FROM @REALMD.account WHERE `last_login`< @DATE

Error occured at:2013-10-20 21:56:11
Line no.:4
Error Code: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@REALMD.account WHERE `last_login`< @DATE' at line 1

i have done lots of modifying and atempts.. so im going through this one at a time
 
Tommy

Tommy

Founder
Minion said:
it does not.. unfortunately. here are some more outputs. sorry im quite frustrated with this.

Code: 
SET @REALMD := 'auth';
SET @CHARACTERS := 'charlk';
SET @DATE := '2012-12-12 00:00:00';
DELETE FROM @REALMD.account WHERE `last_login`< @DATE;
DELETE FROM @CHARACTERS WHERE ( `account` ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `arena_team` WHERE captainGuid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `arena_team_member` WHERE arenaTeamId NOT IN (SELECT arenaTeamId FROM `arena_team` );
DELETE FROM `arena_team_member` WHERE guid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `auctionhouse` WHERE itemowner NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `account_instance_times` WHERE ( `accountId` ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `character_action` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement_progress` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_arena_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_aura` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_data` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_random` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_equipmentsets` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_feed_log` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_gifts` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_glyphs` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_homebind` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_inventory` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_pet` WHERE ( owner ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_daily` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_weekly` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_reputation` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_social` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_talent` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_spell` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_skills` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_tutorial` WHERE ( account ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `corpse` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_bank_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_item` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_right` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_tab` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_member` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_rank` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `item_refund_instance` WHERE ( player_guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `item_refund_instance` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `item_soulbound_trade_data` WHERE `itemGuid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail` WHERE (receiver ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `mail_items` WHERE (mail_id ) NOT IN ( SELECT id FROM `mail` );
DELETE FROM `petition` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition` WHERE (petitionguid ) NOT IN ( SELECT guid FROM `item_instance` );
DELETE FROM `petition_sign` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE playerguid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE petitionguid NOT IN (SELECT petitionguid FROM `petition` );
DELETE FROM `pet_spell` WHERE guid NOT IN (SELECT id FROM `character_pet` );
DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`);
DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character_inventory`) AND `owner_guid`<>'0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);

this offers up:
Code: 
Query:
DELETE FROM @REALMD.account WHERE `last_login`< @DATE

Error occured at:2013-10-20 21:56:11
Line no.:4
Error Code: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@REALMD.account WHERE `last_login`< @DATE' at line 1

i have done lots of modifying and atempts.. so im going through this one at a time
Click to expand...

I tested it and it worked fine, hence why I said it should work for you, but I said "should". :p

You don't need @DATE anyway, just change it back like it was. Also, you don't need @REALMD either.
 
Minion

Minion

Enthusiast
i really appreciate the replies m8. i got annoyed and ran
Code: 
DELETE FROM auth.account WHERE `last_login`< '2012-12-30 00:00:00';
and no surprise it did as it was supposed to do, this leaves me with the unwanted character data. so i have this atm:

Code: 
SET @CHARACTERS := 'charlk';
DELETE FROM auth.account WHERE `last_login`< '2012-12-30 00:00:00';
DELETE FROM @CHARACTERS WHERE ( `accountId` ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `arena_team` WHERE captainGuid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `arena_team_member` WHERE arenaTeamId NOT IN (SELECT arenaTeamId FROM `arena_team` );
DELETE FROM `arena_team_member` WHERE guid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `auctionhouse` WHERE itemowner NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `account_instance_times` WHERE ( `accountId` ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `character_action` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_achievement_progress` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_arena_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_aura` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_data` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_battleground_random` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_equipmentsets` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_feed_log` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_gifts` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_glyphs` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_homebind` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_stats` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_instance`;
DELETE FROM `character_inventory` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_pet` WHERE ( owner ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_daily` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_queststatus_weekly` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_reputation` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_social` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_talent` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_spell` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `character_skills` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `character_spell_cooldown`;
DELETE FROM `character_tutorial` WHERE ( account ) NOT IN ( SELECT id FROM auth.account );
DELETE FROM `corpse` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
TRUNCATE TABLE `creature_respawn`;
TRUNCATE TABLE `gameobject_respawn`;
TRUNCATE TABLE `gm_tickets`;
TRUNCATE TABLE `groups`;
TRUNCATE TABLE `group_instance`;
TRUNCATE TABLE `group_member`;
DELETE FROM `guild_bank_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_item` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_right` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_bank_tab` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_eventlog` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_member` WHERE ( guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `guild_member` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
DELETE FROM `guild_rank` WHERE ( guildid ) NOT IN ( SELECT guildid FROM `guild` );
TRUNCATE TABLE `instance`;
TRUNCATE TABLE `instance_reset`;
DELETE FROM `item_refund_instance` WHERE ( player_guid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `item_refund_instance` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `item_soulbound_trade_data` WHERE `itemGuid` NOT IN (SELECT `guid` FROM `item_instance`);
TRUNCATE TABLE `lag_reports`;
DELETE FROM `mail` WHERE (receiver ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `mail_items` WHERE (mail_id ) NOT IN ( SELECT id FROM `mail` );
DELETE FROM `petition` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition` WHERE (petitionguid ) NOT IN ( SELECT guid FROM `item_instance` );
DELETE FROM `petition_sign` WHERE (ownerguid ) NOT IN ( SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE playerguid NOT IN (SELECT guid FROM @CHARACTERS );
DELETE FROM `petition_sign` WHERE petitionguid NOT IN (SELECT petitionguid FROM `petition` );
TRUNCATE TABLE `pet_aura`;
DELETE FROM `pet_spell` WHERE guid NOT IN (SELECT id FROM `character_pet` );
TRUNCATE TABLE `pet_spell_cooldown`;
DELETE FROM `item_instance` WHERE `guid` NOT IN (SELECT `item` FROM `character_inventory`) AND `guid` NOT IN (SELECT `itemguid` FROM `auctionhouse`) AND `guid` NOT IN (SELECT `item_guid` FROM `guild_bank_item`) AND `guid` NOT IN (SELECT `item_guid` FROM `mail_items`) AND `guid` NOT IN (SELECT `item_guid` FROM `character_gifts`);
DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `character_inventory`) AND `owner_guid`<>'0';
DELETE FROM `character_inventory` WHERE `item` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `auctionhouse` WHERE `itemguid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `guild_bank_item` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `mail_items` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);
DELETE FROM `character_gifts` WHERE `item_guid` NOT IN (SELECT `guid` FROM `item_instance`);

i get this:
Code: 
Query:
DELETE FROM @CHARACTERS WHERE ( `accountId` ) NOT IN ( SELECT id FROM auth.account )

Error occured at:2013-10-20 22:54:45
Line no.:3
Error Code: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@CHARACTERS WHERE ( `accountId` ) NOT IN ( SELECT id FROM auth.account )' at line 1
 
Minion

Minion

Enthusiast
my question here is where is the syntax error.

and "SET @CHARACTERS := 'charlk';" points to the database. not the specific table i want it to point at (which is "characters") where it will find the 'account' column.

so should it not be "SET @CHARACTERS := 'characters';" ?

this is how i learn, so please be patient you have been a great help so far
 
Last edited:
Minion

Minion

Enthusiast
Ok so i think i have ironed most of this out but i cant seem to figure out why this sql query:

Code: 
DELETE FROM charlk.characters WHERE ('account') NOT IN (SELECT 'id' FROM auth.account);


deletes everything in my char database.

suggestions?


ps.

i will be posting this script when im done and it works as intended
 
Tommy

Tommy

Founder
Because the ' ' is making it do that. It requires you to declare the column name without the need for single quotation marks.

I removed that and fixed your query:

Code: 
DELETE FROM charlk.characters WHERE account NOT IN (SELECT id FROM auth.account)
 
Tommy

Tommy

Founder
I recreated what you're trying to do and it deleted specific characters that didn't have an account in the account table. I don't see how it is deleting everything for you when it works fine for me..

I ran the query and this deleted one character that didn't have an account:

The accountId was 1:

YQr594y.png


Characters table afterwards:

Character that had 'account' 1 attached to it is gone and everything else is still there:

Ed9UxWE.png


I've done this with 20 + queries and it isn't wiping the entire table, so I don't know why it does it for you.
 
Status
Not open for further replies.
Top