Jump to content
--> Place your ad text here <--
Get Free Banners - Giveaway MaxCheaters.com
We're Rebuilding – Join Our Staff Team

[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

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing


  • Posts

    • Asuki
      Asuki Dev Client

      By Asuki · Posted

    • SephirothFx
      LineageII FULL configured servers just put ONLINE + FREE WEB

      By SephirothFx · Posted

      My official facebook profile!: https://www.facebook.com/spectrumL2 Specifications: Revamped L2JACIS revision FROM the core Private project!!! Revision that has been receiving corrections for over 3 years!!! Events already installed in the revision: TVT CTF KTB PARTY FARM SPOIL EVENT CRAZY RATES TOURNAMENT TIME ZONE (INSTANCE) All working correctly!!! SIEGE ESSENTIAL FEATURES: Walls fix Gates fix Flags fix 100% functional: OLYMPIADS: Implemented settings Hero receives enchanted Weapons with equal status PvP Weapons Optional /true/false Hero can acquire all Hero Weapons Optional true/false OTHER IMPLEMENTATIONS: Teleport fixed (directly to Giran) Teleport effect classic Vip skins vip collor name Pack NPCs with effect already configured BOSES already configured Mobs already configured CLASS BALANCE SPECIAL SYSTEM We have a SPECIAL system developed for Class Balance with only 1 digit in XML %tage of configurable debuffs Player limitation system in BOSES or PvP zones BS blocking system in FLEG zones or events Among others dozens of improvements made in the review... price: 390 USD !  OBS: WE CAN CHANGE THE BANNER AND NAME OF THE SERVICE TO THE ONE OF YOUR PREFERENCE BUT THE SETTINGS MUST BE KEPT ANY CHANGES REQUIRE ADDITION        
    • Celestine
      [L2J] L2 ROOSTERS ARGENTINA

      By Celestine · Posted

      Server is Online – 1,000+ Active Players! We’re excited to announce the addition of a Europe Proxy to improve connectivity for our EU players! Clans can now benefit from VIP Access to help you catch up faster. 🎯 If you're a clan leader with at least 9 active members, join our Discord and open a ticket to claim your VIP rewards!  
    • SocNet
      SOCNET | The Social Media acocunts at low prices!

      By SocNet · Posted

      The Telegram team is rolling out a new batch of Stars-only gifts you’ll be able to mint as NFTs. Don’t miss your chance to join the next Telegram trend and earn from it! Buy Telegram Stars cheap and KYC-free 1 Star from $0.0149 (min. 50 Stars, bulk discounts available) Promo code STARS5 — 5 % off Pay any way you like: bank cards · crypto · other popular methods How to purchase: ➡Online Store — Click ➡ Telegram bot — Click Other services: ➡ SMM panel — Click Regular buyers get extra discounts and promo codes. Support: ➡ Telegram: https://t.me/solomon_bog ➡ Telegram channel: https://t.me/accsforyou_shop ➡ Discord: https://discord.gg/y9AStFFsrh ➡ WhatsApp: https://wa.me/79051904467 ➡ Email: solomonbog@socnet.store Use these contacts to discuss wholesale orders, partnerships (current list: https://socnet.bgng.io/partners) or to become a supplier. SocNet — your shop for digital goods and premium subscriptions
    • SocNet
      SOCNET | The Social Media acocunts at low prices!

      By SocNet · Posted

      The Telegram team is rolling out a new batch of Stars-only gifts you’ll be able to mint as NFTs. Don’t miss your chance to join the next Telegram trend and earn from it! Buy Telegram Stars cheap and KYC-free 1 Star from $0.0149 (min. 50 Stars, bulk discounts available) Promo code STARS5 — 5 % off Pay any way you like: bank cards · crypto · other popular methods How to purchase: ➡Online Store — Click ➡ Telegram bot — Click Other services: ➡ SMM panel — Click Regular buyers get extra discounts and promo codes. Support: ➡ Telegram: https://t.me/solomon_bog ➡ Telegram channel: https://t.me/accsforyou_shop ➡ Discord: https://discord.gg/y9AStFFsrh ➡ WhatsApp: https://wa.me/79051904467 ➡ Email: solomonbog@socnet.store Use these contacts to discuss wholesale orders, partnerships (current list: https://socnet.bgng.io/partners) or to become a supplier. SocNet — your shop for digital goods and premium subscriptions

  • 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