EdenEternal Posted July 15, 2010 Posted 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 :) Quote
Sinova™ Posted July 18, 2010 Posted July 18, 2010 and why not by hand... it's easier and quickly. lol =/ Quote
EdenEternal Posted July 18, 2010 Author Posted July 18, 2010 and why not by hand... it's easier and quickly. lol =/ this is example noob Quote
Realtek2 Posted July 18, 2010 Posted 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? Quote
EdenEternal Posted July 18, 2010 Author Posted 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)); Quote
EdenEternal Posted July 18, 2010 Author Posted July 18, 2010 im not understand esplica i can't understand you :D what is esplica? :D Quote
EdenEternal Posted July 18, 2010 Author Posted 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)); Quote
Realtek2 Posted July 18, 2010 Posted 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 Quote
killer666 Posted July 18, 2010 Posted July 18, 2010 DELETE FROM `spawnlist` WHERE `npc_templateid` = 7777; This works dude. Quote
minideed Posted July 18, 2010 Posted 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. Quote
assassinhachik Posted July 19, 2010 Posted 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 Quote
EdenEternal Posted July 19, 2010 Author Posted 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? Quote
removed_001 Posted July 19, 2010 Posted 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 Quote
EdenEternal Posted July 19, 2010 Author Posted 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? Quote
removed_001 Posted July 19, 2010 Posted 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 Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.