Welcome to AC Web.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  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
    381
    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
    381
    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,242
    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; 07-25-2021 at 09:44 AM.

  12. #12
    I just saw ur answer now, Tok124 I knew You would reply!!! hahah thanks !

  13. #13


    Join Date
    Jan 2019
    Location
    rural America
    Posts
    381
    @brunolopes -- You'll never learn by relying on others to provide you with explicit answers, at least not learn anything to the point of mastery. You seem like a decently inclined and intelligent individual, you should do yourself a favor and make more of an effort to puzzle things out on your own, it will significantly improve your capabilities in the long run.

    As to the query @Tok124 has generously furnished---and as I pointed you in the direction toward---well, that's about as complex as anything you would ever want to issue to these databases; more than necessary, really. For the most practical solutions, @IWillNeverStop states it best, "Simple way is always better." To quote the Pulitzer Prize winning jounalist, Jon Franklin, "Simplicity, carried to the extreme, becomes elegance." This statement couldn't be more true when it's applied to writing computer code of any language, regardless of application. And, a good thing to keep in mind for all life's endeavors

  14. #14
    Heya Vratam you are totaly right and when i ve made this thread i already had a query of my own but it was so shitty( but it worked), I love how tok124 aproaches SQL and how he writes it so clean thats why i said his name so i can compare his script to mine and learn from it, Sorry if i made you mad or anything but my SQL threads nowdays are not a question of need regarding the utility i give them as i can write them my self but how can i make them better and cleaner

  15. #15


    Join Date
    Jan 2019
    Location
    rural America
    Posts
    381

    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    @brunolopes -- You didn't make me mad I've given you the answer to your question in the quoting above: you improve your code (SQL or whatever, doesn't matter) through simplifying it, which you will only truly be able to do through your own experience. Yes, studying well written code may give you clues to that end, but to be truly successful you need to be thinking in that way, and that only comes through practice. SQL may not be the best example, as a) it's not really a programming language, so there's not really multiple means to a solution, and b) it's a relatively simple language to begin with, without reasonable need for logic or decision making.

    To quote one of the pioneers of computer science, Ken Thompson, referring to the art of simplicity, "One of my most productive days was throwing away 1000 lines of code."

    P.S. - shitty working code could be considered an oxymoron

 

 

Posting Permissions

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