Mysql Query Help, Disable Fabled.

[Figback65]

Well, another question. I tried to use georges tools to edit npcs and it now gives me errors with the new special attacks changes. After i noticed what it was doing, it had ruined my npc_types table and i had to restore from a backup. So here now I am tryen to figure out a simple query to run to help edit them w/o usen tools anymore. Sorry george :( Lovem tho!

SELECT
   npc_types.name AS NPC_Name,
   spawn2.enabled AS Enabled
FROM
   npc_types,
   spawn2
WHERE
   npc_types.name LIKE '%fabled%';

This errors out with out of memory error

and

SELECT
   npc_types.name AS NPC_Name,
   spawn2.enabled AS Enabled
FROM
   npc_types,
   spawn2
WHERE
   npc_types.name LIKE '%fabled%'
LIMIT 100;

Just repeats the 1st Fabled string it finds, which is 100 Fabled Fippys.

Any ideas or opinions? And yes i try try try and research like crazy, even stack overflow and forums before i bother you guys

[Figback65]

Oh and I tried to build off this Faction query i found somewhere on these forums, but hell it just confused the shit outta me, Looks like it makes a temp table or something then merges the info with INNER JOIN but that is still beyond me.

SELECT
   npc_faction.id AS FactionTableID,
   npc_faction.name AS FactionTableName,
   primaryfaction AS PrimaryFactionID,
   factionA.name AS PrimaryFactionName,
   npc_faction_entries.faction_id AS FactionEntryID,
   factionB.name AS FactionEntryName,
   npc_faction_entries.value AS FactionEntryValue,
   npc_faction_entries.npc_value AS FactionEntryNpcValue
FROM
   npc_faction
   INNER JOIN faction_list factionA
     ON factionA.id=primaryfaction
   INNER JOIN npc_faction_entries
     ON npc_faction_entries.npc_faction_id=npc_faction.id
   INNER JOIN faction_list factionB
     ON factionB.id=npc_faction_entries.faction_id
WHERE
   npc_faction.id=112

[Zaela_S]

There are no meaningful natural matches between npc_types and spawn2, it's probably joining on random columns with matching 1s and 0s and such in the first one or something.

NPCIDs are associated with spawn2's by way of spawnentry's, so maybe something like this:

SELECT npc_types.name, spawn2.enabled, spawn2.id 
FROM npc_types 
JOIN spawnentry ON spawnentry.npcID = npc_types.id 
JOIN spawn2 ON spawn2.spawngroupID = spawnentry.spawngroupID 
WHERE npc_types.name LIKE '%Fabled%';

[chrsschb]

What are you trying to accomplish? Removing fabled spawns from the spawngroups?

[Figback65]

I am using this as a template for many things, but primarly right now I am trying to just pull up all the fabled in npc_types.name and spawn2.enabled in a query so i can disable them by changing enabled to 0 from 1 in the spawn2 table manually. With the query, if i could link them somehow then I wouldnt have to go through and find all the spawn #s and whatnot to match them each up manually. I do not want to totally remove them, just disable So I can use them for later if needed.

[Figback65]

Thank you Zaela_S!!!
That 100% worked! That will also help me learn on how to JOIN tables needed so I can link them together to pull what I need, which i did not know you had to do that.

Soo much to learn in the emu world lol

EDIT : I am so used to the easy way of GeorgeS npc_edit tools, its going to be rough getting used to linking everything together to adjust loot, spawns groups, all of the wonderful stuff he made easier. I hope theres an update soon!

[Figback65]

Ok I was wrong, disabling them in spawn2, disables the entire spawn table linked to the fable. So none of the normal mobs spawn as they should. Guess, need to find the spawngroup part.

EDIT : Ya was def wrong, that lets me know if the spawn group is enabled. I will work with the example u given you for sure and try to come up with one that displays everything and post it here for people to use. ITs gotta link the spawngroup ID with the spawnentry spawngroupID and then of course link with npc_types so u can see the name.

[Figback65]

Alright, tweaked it a bit and got a pretty nice one going. I noticed GeorgeS updated also(shoulda checked sooner) So paired with his popup Spawngroup editor and this query, also clicking the X by the zone name on the spawngroup popup so it turns to all. You can copy the ID from the query and paste it in NPCID in georges, then quickly edit the spawn rate or disable. The query also shows you the spawn chance and if the spawngroup is enabled. Saved me tons of time locating the fabled and adjusting the spawngroup spawnrating to = 100% rotation, and disabling things like exterminators and more. It also shows you the zone they spawn in so you dont waste time looking at each one.


To search, just change "Exterminator_" to the search NPCName(keyword) you are looking for. Example, Fabled, Exterminator, Magus, whatever you want, full or partial names.

SELECT npc_types.id, npc_types.name, npc_types.lastname, spawn2.zone, spawn2.enabled, spawn2.id, spawnentry.spawngroupID, spawnentry.chance
FROM npc_types 
JOIN spawnentry ON spawnentry.npcID = npc_types.id 
JOIN spawn2 ON spawn2.spawngroupID = spawnentry.spawngroupID
WHERE npc_types.name LIKE '%Exterminator_%'
AND spawnentry.chance > '0';

EDIT : Added npc_types.lastname to SELECT list so you can change the WHERE npc_types.name to npc_types.lastname and change WHERE to npc_types.lastname and search for beserker to quickly cleanup all beserker spawns(GMs,Tome Merchants) if you are cleaning them out or any other search by last name.

[Bohbo]

I am waiting on George's tool to be resurrected as well..
I came up with this query to still edit stuff based on zone.

UPDATE npc_types
JOIN spawnentry on spawnentry.npcID = npc_types.id
JOIN  spawn2 on spawnentry.spawngroupID = spawn2.spawngroupID
#SET texture =  (FLOOR(0 + (RAND() * (4 - 0)))) #sets a random texture BETWEEN 0 AND 4 
SET armortint_id = 8
WHERE spawn2.zone like '%mistmoore%'  and npc_types.id < 9900000 and race = 6 and armortint_id = 3

Just replace set armortint_id to whatever else you want and edit the where to sit your needs.

[Kilur]

So can I use this sql to remove the fabled from my server?