EQEmulator Forums - 3 failed SQL Attempts to insert a value + a select statement result

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- Support::Windows Servers (https://www.eqemulator.org/forums/forumdisplay.php?f=587)

- - 3 failed SQL Attempts to insert a value + a select statement result (https://www.eqemulator.org/forums/showthread.php?t=39341)

3 failed SQL Attempts to insert a value + a select statement result

I am trying to take a task ID using 350 in this example. And based on a select criteria in this case looking at whether or not a mob is non named, race 455, in warrens.

So the idea is you can test this with a select * from npc_types to test your selection statement. Once you have that correct take the list of IDs select id from npc_types and insert that + fixed value of 350 into the goal lists table.

Any tips here to get any of the methods working would be appreciated. I feel like its a good teachable moment through my 3 examples of failure.

Code:

foreach(SELECT id 

FROM npc_types 

      inner join spawnentry on npc_types.id = spawnentry.npcID 

                inner join spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID

                where zone = 'warrens' and race = 455 and name not like '#%'

                , 

                insert into goallists (listid, entry)                

                values (350,id))

Code:

insert into goallists (listid, entry)



set @goalid=

SELECT id 

FROM npc_types 

      inner join spawnentry on npc_types.id = spawnentry.npcID 

                inner join spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID

                where zone = 'warrens' and race = 455 and name not like '#%'

insert into goallists (listid, entry)                

values (350,@goalid)

Code:

insert into goallists (listid, entry)



SELECT id 

FROM npc_types 

      inner join spawnentry on npc_types.id = spawnentry.npcID 

                inner join spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID

                where zone = 'warrens' and race = 455 and name not like '#%'

                

values (350,id)

http://www.w3schools.com/sql/sql_insert_into_select.asp

Don't fully understand what you're trying to do, but maybe something like:

Code:

INSERT INTO goallists (listid, entry)

SELECT 350, npc_types.id FROM npc_types

JOIN spawnentry ON npc_types.id = spawnentry.npcID

JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID

WHERE zone = 'warrens' AND race = 455 AND name NOT LIKE '#%'

Quote:

Originally Posted by Zaela_S (Post 237685)

http://www.w3schools.com/sql/sql_insert_into_select.asp

Don't fully understand what you're trying to do, but maybe something like:

Code:

INSERT INTO goallists (listid, entry)

SELECT 350, npc_types.id FROM npc_types

JOIN spawnentry ON npc_types.id = spawnentry.npcID

JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID

WHERE zone = 'warrens' AND race = 455 AND name NOT LIKE '#%'

That nearly did it! So sometimes i want to make a task to kill any mob in a zone. So i used a goallist and added all the mobs. I wanted to have a sql script handle it. Or say a subset like Kill all skeletons.

EDIT:: the only reason that didn't work was i got a duplicate key value, but i see how the select 350, search works for the insert now thanks

EDIT::: is there a way to have it return only unique id / key values?

EDIT:::: This works perfectly and filters out doubles!

Code:

INSERT INTO goallists (listid, entry)

SELECT 350, npcid 

FROM npc_types

JOIN spawnentry ON npc_types.id = spawnentry.npcID

JOIN spawn2 ON spawnentry.spawngroupID = spawn2.spawngroupID

WHERE zone = 'warrens' AND race = 455 AND name NOT LIKE '#%'

group by npcid