Jump to content
MaxCheaters Official Group on Discord
-> Place your ad text here <-
LINEAGE2.GOLD INTERLUDE SERVER ON ESSENCE CLIENT

[SQL]Question - Answer

EdenEternal
 Share

Recommended Posts

EdenEternal Newbie

EdenEternal

Posted
Posted

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 :)

EdenEternal Newbie

EdenEternal

Posted
  • Author
Posted

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 Newbie

EdenEternal

Posted
  • Author
Posted

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 Newbie

Realtek2

Posted
Posted 

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

minideed Newbie

minideed

Posted
Posted

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.

EdenEternal Newbie

EdenEternal

Posted
  • Author
Posted 

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 Newbie

removed_001

Posted
Posted

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 Newbie

EdenEternal

Posted
  • Author
Posted

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 Newbie

removed_001

Posted
Posted

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing


  • Posts

    • SilaVodi
      adena top price

      By SilaVodi · Posted

      Hello everyone, we are one of the top gaming currency stores. We work exclusively with top projects. If you are interested in anything like Adena, Coins, Equip, write to us Discord - pchelacoin Telegram - https://t.me/ipchelacoin BOHPTS, KETRAWARS, EURO-PVP, L2REBORN, E-GLOBAL, LA2DREAM TOP PRICE !!!!!!!
    • Databay
      █ 7,000,000+ RESIDENTIAL PROXIES ✅ FROM 0.65$ / GB █

      By Databay · Posted

      This is a bump: https://databay.com/
    • L2ElixirOfficial
      [L2J] L2Elixir

      By L2ElixirOfficial · Posted

      L2Elixir – Patch 4 Is Live!   We’re working non-stop, day and night, to deliver the best possible quality and bring back what made L2Elixir special. This project is built with passion, not shortcuts — for the old-school players who remember, and the new ones who want to experience it properly. Thank you for being part of the journey. Together, we’re making L2Elixir great again ❤️ The legends never fade.    ⚙️ General Enabled Class Change service (same class type only) ALT + B → Services → Character Development Enabled Shift + Click on Treasure Chests Players can now identify real chests (Adena, scroll drops) and use Key / Unlock Event deaths now cancel only debuffs, All self buffs are preserved, fixes issues with Root and similar effects Bladedancer class can now log in even when Max Clients (2) is reached. Since an active Bladedancer is not available for every damage dealer and some players tried to abuse this via VPN or a second PC, this feature was added to keep things fair. protections applies, requires testing!    🎒 Items Crystallizing enchanted items now gives the correct increased crystal amount (retail-like behavior) Removed Agathion Seal Bracelet: Rudolph from Santa rewards (Gracia Final item) Added Dualsword Craft Stamp into Milestone Exchange list    🧙 Skills Fixed Banish Undead lethal chance Hot Springs Malaria and similar effects now level up faster while being attacked
    • Nightw0lf
      [L2J] L2Relic

      By Nightw0lf · Posted

      thats new SEO level tricks you know nothing of noob - bottom line: exposed.
    • FixerRay
      PchelaCoin Top Market Adena/Coin

      By FixerRay · Posted

      Warning: This guy is a big scammer, trying to sell everything, advertising for servers etc. That's his mail address evgesha.nrnr@gmail.com , stay away!   @Atom @Celestine

  • Topics

×
×
  • 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