[SOLVED] SQL NPC_vendor

[Barebones]

I'm wondering if there is a way to add all items with a specific value, such as ilvl #, into a vendor where entry is #. This is what I have:

SET @NPC_Vendor_Entry = 500146;
SET @NPC_Vendor_Slot = 0;
SET @NPC_Vendor_Item = select entry from item_template where itemlevel = 123;
SET @NPC_Vendor_Maxcount = 0;
SET @NPC_Vendor_incrtime = 0;
SET @NPC_Vendor_Extendedcost = 0;

insert into npc_vendor ('entry', 'slot', 'item', 'maxcount', 'incrtime', 'extendedcost') values (@NPC_Vendor_Entry, @NPC_Vendor_Slot, @NPC_Vendor_Item, @NPC_Vendor_Maxcount, @NPC_Vendor_incrtime, @NPC_Vendor_Extendedcost)

I get an error with the 'select entry from...'

 

[Tommy]

This will work, except if there's more than one result it will error saying that the SubQuery returns more than one result..

SET @NPC_Vendor_Entry = 500146;
SET @NPC_Vendor_Slot = 0;
SET @NPC_Vendor_Maxcount = 0;
SET @NPC_Vendor_incrtime = 0;
SET @NPC_Vendor_Extendedcost = 0;

INSERT INTO `npc_vendor` VALUES (@NPC_Vendor_Entry, @NPC_Vendor_Slot, (SELECT `entry` FROM `item_template` WHERE ItemLevel=123), @NPC_Vendor_Maxcount, @NPC_Vendor_incrtime, @NPC_Vendor_Extendedcost);

 

[Barebones]

This will work, except if there's more than one result it will error saying that the SubQuery returns more than one result..

Yeah, I'm trying to get around that :T

 

[Rochet2]

You need to use () around the SELECT blah blah code in the SET.
See: http://pastebin.com/BybD39nN

Also use LIMIT 1 if multiple results is an issue..
And use order by or max or min if you want a specific value.

 

[Barebones]

Limit 1 is an issue because 1. It throws an error and 2. I want more than one item.

 

[Rochet2]

Ok ok, I didnt read the main post : |
My fault.
Its really easy really:

SET @NPC_Vendor_Entry = 500146;
SET @NPC_Vendor_Slot = 0;
SET @NPC_Vendor_Maxcount = 0;
SET @NPC_Vendor_incrtime = 0;
SET @NPC_Vendor_Extendedcost = 0;
SET @ITEMLEVEL = 123;

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `extendedcost`)
SELECT @NPC_Vendor_Entry, @NPC_Vendor_Slot, item_template.entry, @NPC_Vendor_Maxcount, @NPC_Vendor_incrtime, @NPC_Vendor_Extendedcost FROM `item_template` WHERE `itemlevel` = @ITEMLEVEL;

Edit: added item_template to not make entry ambiguous

 

[Barebones]

SET @NPC_Vendor_Entry = 500146;
SET @NPC_Vendor_Slot = 0;
SET @NPC_Vendor_Maxcount = 0;
SET @NPC_Vendor_incrtime = 0;
SET @NPC_Vendor_Extendedcost = 0;
SET @ITEMLEVEL = 123;

INSERT INTO `npc_vendor` (`entry`, `slot`, `item`, `maxcount`, `incrtime`, `extendedcost`)
SELECT @NPC_Vendor_Entry, @NPC_Vendor_Slot, item_template.entry, @NPC_Vendor_Maxcount, @NPC_Vendor_incrtime,[COLOR="#FF0000"] @NPC_Vendor_Extendedcost FROM `item_template` WHERE `itemlevel` = @ITEMLEVEL[/COLOR];

Why is like that?

 

[Rochet2]

Well, the code inserts a new row for all matches in the SELECT statement.
So the red part is like that since it is a SELECT statement, not a VALUES. SELECT usually provides the table and possibly a WHERE statement, no?

Its kind of like doing what tommy suggested, but doing it automatically, its normal SQL.
Not really much I can say to tell you why, when it is like that since that is how it works .. : |
Unsure what you are asking exactly

http://www.w3schools.com/sql/sql_insert_into_select.asp
Google sql insert into select?

 

[Barebones]

Why isn't it next to the item_template.entry is what I'm asking.

 

[Rochet2]

Since if it was done like tommy's, you run into the problem that you are trying to insert ONE row.
And do note the difference with VALUES and SELECT.
You are supposed to make a select statement that returns the values you want to insert.

If you didnt know, you can do:

SELECT 5;
and it returns 5.
Similarily all the variables you saved with SET will be placed to the SELECT statement and will be returned.

Example of a simple code:
INSERT INTO mytable (`mycolumn`)
SELECT 5 FROM othertable WHERE something = 6;

This inserts as many times 5 to mytable as rows are returned by the SELECT statement.
This is how using insert into with select instead of values works.


It is like that since that is how SELECT statement works. And that is how SELECT statement works with insert into to insert multiple rows.