Welcome to AC Web.
Results 1 to 11 of 11
  1. #1

    Need help on a SQL


    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Heya folks I come here with a question, lets say i have 4 items (id 1,11,2,22) that i want to remove from every loot template&vendor, I know how to this manualy but i cant figure out how to make a query that would do such thing, your help is apreciated thanks!

  2. #2
    DELETE FROM npc_vendor WHERE item IN (1,11,2,22);

  3. #3
    I dont think that works mate

  4. #4


    Join Date
    Jan 2019
    Location
    rural America
    Posts
    353
    You have to join the npc_vendor table with all the *_loot_template tables, using item as the common field. Best run a select statement before your delete, just to make sure you've got the join correct :-)

  5. #5
    Where is Tok123 when we need him HUGE LOL PS- he is the best always helps me with SQL

  6. #6
    DELETE FROM npc_vendor WHERE item IN(x1, x2);
    DELETE FROM creature_loot_template WHERE item IN(x1, x2);

  7. #7


    Join Date
    Jan 2019
    Location
    rural America
    Posts
    353
    Quote Originally Posted by IWillNeverStop View Post
    DELETE FROM npc_vendor WHERE item IN(x1, x2);
    DELETE FROM creature_loot_template WHERE item IN(x1, x2);
    He's looking for a single query...I told him how to go about it, but apparently he wants it written out for him. Personally, I agree with you...put it in a script and execute the file, it couldn't be simpler :-)

  8. #8
    Simple way is always better
    Last edited by IWillNeverStop; 07-23-2021 at 03:40 AM.

  9. #9
    If that does not solve what you ask then I do not know what you want

  10. #10
    thats exactly it i just didng know how to write this part { IN(x1, x2);} thanks!

  11. #11
    Web/SQL Dev & 3D Artist

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    3,209

    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Quote Originally Posted by brunolopes View Post
    Where is Tok123 when we need him HUGE LOL PS- he is the best always helps me with SQL
    I think you meant tok124? xD

    - - - Updated - - -

    Anyway the query linked by elperro works just fine and as vratam said you can use JOIN/INNER JOIN. I would personally just write multiple queries like the one elperro wrote but if you do wanna use the INNER JOIN method you could write it like this
    Code:
    DELETE nv.*, clt.*, ilt.*, rlt.*, glt.*, dlt.*, flt.*, plt.*, slt.*, splt.* FROM npc_vendor nv
    INNER JOIN creature_loot_template AS clt
    ON clt.item = nv.item
    INNER JOIN item_loot_template AS ilt
    ON ilt.item = nv.item
    INNER JOIN reference_loot_template AS rlt
    ON rlt.item = nv.item
    INNER JOIN gameobject_loot_template AS glt
    ON glt.Item = nv.item
    INNER JOIN disenchant_loot_template AS dlt
    ON dlt.item = nv.item
    INNER JOIN fishing_loot_template AS flt
    ON flt.item = nv.item
    INNER JOIN prospecting_loot_template AS plt
    ON plt.Item = nv.item
    INNER JOIN skinning_loot_template AS slt
    ON slt.item = nv.item
    INNER JOIN spell_loot_template AS splt
    ON splt.item = nv.item
    WHERE nv.item IN (1, 11, 2, 22);
    I guess your problem is solved by now anyway but yeah, i'm just posting this if you ever need this or anything similar in the future. But i strongly recommend you to make a backup of each table selected in the query before you run it. Im not sure if i have made a mistake or not. I have not tested the query myself
    Last edited by Tok124; Today at 09:44 AM.

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •