Jump to content
MaxCheaters Official Group on Discord
Top.MaxCheaters.com is now OPEN – Lineage 2 Top Listing

[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

    • D2RHiddenStash
      D2R Hiddenstash

      By D2RHiddenStash · Posted

      NEW HIDDENSTASH KEY SYSTEM INTRODUCED TO THE SITE   **Earn While You Spend - Introducing HS Cashback!**   Every purchase on our site now rewards you with **HS Keys cashback**   EVERY ONE WHO REGISTERS IN SITE UNTILL 15TH OF MAY GETS 2000 HS KEYS IN HES BALANE   Here's how it works:       **1 USD = 1000 HS Keys**   **Get 3% cashback** on every purchase   **Use your HS Keys to **save on your next order**   ---   ### ⚡ Why this is awesome   * Every order gives you value back   * Stack it with promos & HS usage   * Turn your spending into future discounts   ---   ### Example   Spend **$10** → Get **300 HS Keys** back   Spend **$50** → Get **1500 HS Keys** back   ---   ### Smart system (built for fairness)   * Cashback is rounded to keep things balanced   * Prevents abuse from tiny orders   * Rewards real buyers   ---   ### Start earning now   Every purchase = progress toward your next discount   Shop now and build your HS balance!   #cashback #gamingdeals #d2r #rewards #loyalty   Stay safe out there, heroes - and happy hunting! www.d2rhiddenstash.com     We just launched our new Affiliate Program — and it’s the easiest way to earn HS Keys.   Invite your friends using your personal link.   Example: If your friend spends $10 → you get 300 HS Keys No limits. No effort. Just share your link.   Get your referral link here: www.d2rhiddenstash.com/profile     Start earning today
    • Gawric
      Client Unity l2

      By Gawric · Posted

      https://jumpshare.com/share/L45ApA5PVrGN2O5Ua5pQ   Skill synchronization with the server: Launching and synchronizing animations, launching and synchronizing effects. All of this is tied to the server's timing  
    • L2Haruna-Network
      Hello, I’ll get straight to the point.

      By L2Haruna-Network · Posted

      java files or l2off?
    • RMTgold
      Buying & Selling Torn City Cash

      By RMTgold · Posted

      PAYPAL& BINANCE
    • RMTgold
      Buying & Selling WOW Gold

      By RMTgold · Posted

      PAYPAL& BINANCE

  • Topics

×
×
  • Create New...

Important Information

This community uses essential cookies to function properly. Non-essential cookies and third-party services are used only with your consent. Read our Privacy Policy and We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue..