Welcome to AC Web.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Sql Problem

  1. #1

    Sql Problem


    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Hello guys what i want exactly to do is add into a vendor ID 55555 all the items where name starts with Ruthless and i made this query and it is not working and i cant figure out how to fix it....


    @creatureentry = (SELECT max(entry)+1 FROM creature_template WHERE entry= 55555),
    @name = "Ruthless",


    INSERT INTO npc_vendor (entry, item)
    SELECT @creatureentry, entry FROM item_template WHERE name = @name



    Thanks in advance!

    - - - Updated - - -

    Also tryed like this


    SET @Entry :=55555;
    INSERT INTO `npc_vendor` (`entry`, `item`)
    SELECT @Entry, select FROM item_template WHERE name like "%Ruthless%"); and it doesnt work
    Last edited by brunolopes; 09-12-2017 at 04:56 AM.

  2. #2


    Join Date
    Dec 2015
    Location
    Developing the world
    Posts
    792
    SET @Entry :=55555;
    INSERT INTO `npc_vendor` (`entry`, `item`)
    SELECT @Entry, select FROM item_template WHERE name like "%Ruthless%");
    this part should not have the red part and if the name starts with Ruthless, then you must do "Ruthless%"

    Please reply me If it doesn't work!
    Thanks for the attention, titkata_bg

  3. #3


    Join Date
    Mar 2016
    Location
    Chaos is the score upon which reality is written.
    Posts
    771
    Of course it doesn't work if you don't know what you're doing.
    Where's the logic in your queries?
    Quote Originally Posted by brunolopes View Post
    @creatureentry = (SELECT max(entry)+1 FROM creature_template WHERE entry= 55555),
    @name = "Ruthless",

    INSERT INTO npc_vendor (entry, item)
    SELECT @creatureentry, entry FROM item_template WHERE name = @name
    Quote Originally Posted by brunolopes View Post
    SET @Entry :=55555;
    INSERT INTO `npc_vendor` (`entry`, `item`)
    SELECT @Entry, select FROM item_template WHERE name like "%Ruthless%");

    Here's my approach.

    Code:
    -- 1. Set the vendor's entry
    Set @VendorID := 55555;
    
    -- 2. Add to npc_vedor table all items where name column contains 'Ruthless'
    -- this will also add entry = 0 to npc_vendor
    INSERT INTO `world`.`npc_vendor` (`item`)
    SELECT `entry` FROM `world`.`item_template` WHERE name like '%Ruthless%';
    
    -- 3. Update npc_vendor table where entry = 0
    UPDATE `world`.`npc_vendor` SET `entry` = @VendorID WHERE `entry` = 0;
    May not be the best option, but at least it works.
    Last edited by mindsear; 09-12-2017 at 09:13 AM.

  4. #4
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    2,774
    Quote Originally Posted by mindsear View Post
    Of course it doesn't work if you don't know what you're doing.
    Where's the logic in your queries?





    Here's my approach.

    Code:
    -- 1. Set the vendor's entry
    Set @VendorID := 55555;
    
    -- 2. Add to npc_vedor table all items where name column contains 'Ruthless'
    -- this will also add entry = 0 to npc_vendor
    INSERT INTO `world`.`npc_vendor` (`item`)
    SELECT `entry` FROM `world`.`item_template` WHERE name like '%Ruthless%';
    
    -- 3. Update npc_vendor table where entry = 0
    UPDATE `world`.`npc_vendor` SET `entry` = @VendorID WHERE `entry` = 0;
    May not be the best option, but at least it works.
    A better way to make it would be like this
    Code:
    SET @VendorID := 55555;
    
    INSERT INTO npc_vendor (entry, item)
    SELECT @VendorID, entry FROM item_template WHERE name LIKE "Ruthless%";
    - - - Updated - - -

    Also, The `` symbols is only used to tell the sql server that the code it wraps is a column or a table or database and not a function. So if you have a column with same name as a function in sql then you would need to use `` otherwise its not needed to ever use them. But for this query there is no column that matches any functions in SQL. And you can see when you write the code in heidisql that the column name changes color if it matches any sql function so then you need to add ``.
    Last edited by Tok124; 09-12-2017 at 09:44 AM.

  5. #5


    Join Date
    Mar 2016
    Location
    Chaos is the score upon which reality is written.
    Posts
    771
    Quote Originally Posted by Tok124 View Post

    - - - Updated - - -

    Also, The `` symbols is only used to tell the sql server that the code it wraps is a column or a table or database and not a function. So if you have a column with same name as a function in sql then you would need to use `` otherwise its not needed to ever use them. But for this query there is no column that matches any functions in SQL. And you can see when you write the code in heidisql that the column name changes color if it matches any sql function so then you need to add ``.
    I know what's the use of ` (grave accent symbol), but I think it's good practice to use them all the time, it doesn't hurt though.
    Although, there's a minimal chance for a column to have the same name as a function, but as I said, it's good practice to use grave accent symbols all the time, especially if you don't use gui applications.
    Last edited by mindsear; 09-12-2017 at 09:57 AM.

  6. #6
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    2,774
    Quote Originally Posted by mindsear View Post
    I know what's the use of ` (grave accent symbol), but I think it's good practice to use them all the time, it doesn't hurt though.
    Although, there's a minimal chance for a column to have the same name as a function, but as I said, it's good practice to use grave accent symbols all the time, especially if you don't use gui applications.
    Yeah ofcourse, It doesnt hurt to use them I personally hate to write code with them but i guess its different from each person

  7. #7


    Join Date
    Mar 2016
    Location
    Chaos is the score upon which reality is written.
    Posts
    771
    Quote Originally Posted by Tok124 View Post
    Yeah ofcourse, It doesnt hurt to use them I personally hate to write code with them but i guess its different from each person
    I also used to hate writing code with grave accent symbols until I got errors and didn't know what the hell was going on.
    Last edited by mindsear; 09-12-2017 at 10:24 AM.

  8. #8
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    2,774
    Quote Originally Posted by mindsear View Post
    I also used to hate writing code with grave accent symbols until I got errors and didn't know what the hell was going on.
    Haha yeah. I have never got errors when i write the code in the query editor in heidisql but i have got errors when i write php code with SQL Querys and yeah... It took me a while to figure out the reason hahaha

  9. #9


    Join Date
    Jan 2017
    Location
    https://www.twitch.tv/artamedes
    Posts
    454
    That symbol is a coding standard in trinitycore if you make a pull request

  10. #10
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    2,774
    Quote Originally Posted by surrenderat20 View Post
    That symbol is a coding standard in trinitycore if you make a pull request
    You mean like if i export rows from a table? That is not only for TrinityCore. Its for any tables in the database and i guess that is just used to be sure that the query wont give an error. But when writing query i personally prefer writing without it. But as both me and mindsear said it doesnt hurt to use them and it doesnt hurt to not use them unless you have a column/table/db with same name as a function.

    So if i have a column called select then yeah... Then i would need it otherwise it would give an error. So yeah if you are not sure of all SQL Functions then its recommended to use them to prevent any errors.

    - - - Updated - - -

    And the exporting function is easyer to code for who ever made the database editor. Otherwise i guess they would need to make an array of all functions and do a function to add it to all that exists in the array and not add it for anything that doesnt exist in the array. And yeah thats useless piece of code to add since it doesnt hurt to use it

  11. #11


    Join Date
    Jan 2017
    Location
    https://www.twitch.tv/artamedes
    Posts
    454
    I'm just saying it is

  12. #12
    Quote Originally Posted by Tok124 View Post
    A better way to make it would be like this
    Code:
    SET @VendorID := 55555;
    
    INSERT INTO npc_vendor (entry, item)
    SELECT @VendorID, entry FROM item_template WHERE name LIKE "Ruthless%";
    - - - Updated - - -

    Also, The `` symbols is only used to tell the sql server that the code it wraps is a column or a table or database and not a function. So if you have a column with same name as a function in sql then you would need to use `` otherwise its not needed to ever use them. But for this query there is no column that matches any functions in SQL. And you can see when you write the code in heidisql that the column name changes color if it matches any sql function so then you need to add ``.
    I used this query and it worked perfectly thanks! I Dont know much about SQL what i tryed was mixing code of other querrys in a way that it made some kind of sence to me thanks!

  13. #13
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    2,774
    Quote Originally Posted by brunolopes View Post
    I used this query and it worked perfectly thanks! I Dont know much about SQL what i tryed was mixing code of other querrys in a way that it made some kind of sence to me thanks!
    Hehe yeah but it made no sense to the database xD But well. Its impossible to be expert in everything We are all good at something and not so good at something else. And the only way to get better is by training

  14. #14
    Quote Originally Posted by Tok124 View Post
    Hehe yeah but it made no sense to the database xD But well. Its impossible to be expert in everything We are all good at something and not so good at something else. And the only way to get better is by training
    Indeed with the query u gave me i could edit it and change other things around the database so you really helped me a lot!

  15. #15


    Join Date
    Dec 2015
    Location
    Developing the world
    Posts
    792

    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Can we stop talking about a symbol, thanks!: )

 

 

Posting Permissions

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