[SQL]Question - Answer

EdenEternal · July 15, 2010

Hello Maxcheaters!

I decide to open topic where users can post their sql question.

Example:

Q.How to Delete from spawnlist my GK(7777)?

A. SQL Script:

DELETE FROM spawnlist WHERE npc_templateid="7777";

i think is usefull topic for Newbie's and not only :)

Sinova™ · July 18, 2010

and why not by hand... it's easier and quickly. lol =/

EdenEternal · July 18, 2010

and why not by hand... it's easier and quickly. lol =/

this is example noob

Realtek2 · July 18, 2010

Q. How can I delete player that is not active more then 1 month.

I mean complete delete - quests/items/shortcuts/skills/subclasses of that player?

EdenEternal · July 18, 2010

Q. How can I delete player that is not active more then 1 month.

I mean complete delete - quests/items/shortcuts/skills/subclasses of that player?

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_friends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_hennas WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_macroses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recipebook WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recommends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM heroes WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM olympiad_nobles WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM augmentations WHERE item_id=(SELECT item_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_bid WHERE clan_name=(SELECT clan_name FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_watch WHERE charObjId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_privs WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_subpledges WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan1=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan2=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
UPDATE clanhall SET ownerId="0" WHERE ownerId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM pets WHERE item_obj_id=(SELECT object_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM seven_signs WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM siege_clans WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));

EdenEternal · July 18, 2010

im not understand esplica

i can't understand you :D what is esplica? :D

EdenEternal · July 18, 2010

Q. How can I delete player that is not active more then 1 month.

I mean complete delete - quests/items/shortcuts/skills/subclasses of that player?

Updated

DELETE FROM characters WHERE lastAccess < 155520000;
DELETE FROM character_quests WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_friends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_shortcuts WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_hennas WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_macroses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recipebook WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_recommends WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_skills_save WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM character_subclasses WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM heroes WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM olympiad_nobles WHERE char_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM augmentations WHERE item_id=(SELECT item_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_bid WHERE clan_name=(SELECT clan_name FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM auction_watch WHERE charObjId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM clan_privs WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_subpledges WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan1=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM clan_wars WHERE clan2=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
UPDATE clanhall SET ownerId="0" WHERE ownerId=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM pets WHERE item_obj_id=(SELECT object_id FROM items WHERE owner_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));
DELETE FROM seven_signs WHERE char_obj_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000);
DELETE FROM siege_clans WHERE clan_id=(SELECT clan_id FROM clan_data WHERE leader_id=(SELECT obj_id FROM characters WHERE lastAccess < 155520000));

Realtek2 · July 18, 2010

ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1146 : Table 'test-server.character_friends' doesn't exist
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1146 : Table 'test-server.augmentations' doesn't exist
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'

thats what I get :S

killer666 · July 18, 2010

DELETE FROM `spawnlist` WHERE `npc_templateid` = 7777;

This works dude.

minideed · July 18, 2010

All you need to do is execute this line:

DELETE FROM characters WHERE lastAccess < 155520000;

When you reboot the server most java cores will automatically delete all information in the database for whatever character(s) were deleted. No need for all those lines of code, too much wasted time and since not all L2j database tables are the same it probably will return errors.

assassinhachik · July 19, 2010

I'am so sorry for off top :-[

Leki maybe you also know how to optimize MySQL config file? for better performance with l2jserver

EdenEternal · July 19, 2010

ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1146 : Table 'test-server.character_friends' doesn't exist
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1054 : Unknown column 'char_id' in 'where clause'
ERROR 1146 : Table 'test-server.augmentations' doesn't exist
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'obj_id' in 'field list'
ERROR 1054 : Unknown column 'char_obj_id' in 'where clause'
ERROR 1054 : Unknown column 'obj_id' in 'field list'

thats what I get :S

it's because we have different packs. i have la2base IL, and that sql query is working for me. which pack you have?

removed_001 · July 19, 2010

Hello Maxcheaters!

I decide to open topic where users can post their sql question.

Example:

Q.How to Delete from spawnlist my GK(7777)?

A. SQL Script:
DELETE FROM spawnlist WHERE npc_templateid="7777";
i think is usefull topic for Newbie's and not only :)

come on only 3 lines share???? COME ON its pretty easy with right click delete record better than writing these facking things

EdenEternal · July 19, 2010

come on only 3 lines share???? COME ON its pretty easy with right click delete record better than writing these facking things

for example you have monster with id 99999

and it's spawned 5000 Places. you will delete all manual?

or with sql script?

removed_001 · July 19, 2010

for example you have monster with id 99999

and it's spawned 5000 Places. you will delete all manual?

or with sql script?

u can just select all the lines come on its pretty easy (set the begining and the ending and delete records

[SQL]Question - Answer

Recommended Posts

EdenEternal

Sinova™

EdenEternal

Realtek2

EdenEternal

EdenEternal

EdenEternal

Realtek2

killer666

minideed

assassinhachik

EdenEternal

removed_001

EdenEternal

removed_001

Join the conversation

Posts

Topics

Browse

Activity

Store