UPDATE mob, (SELECT * FROM
(SELECT mob.mob_id, mob.name, mob.npctemplateid FROM mob LEFT JOIN npctemplate
ON mob.npctemplateid = npctemplate.templateid WHERE npctemplate.templateid IS NULL) a
LEFT JOIN
(SELECT mob.npctemplateid as fnpctid, mob.name as fname from mob JOIN npctemplate on mob.npctemplateid = npctemplate.templateid GROUP BY npctemplate.templateid) b
ON a.name = b.fname
LEFT JOIN
(SELECT npctemplate.name as lname, npctemplate.templateid as lnpctid FROM npctemplate GROUP BY npctemplate.templateid) c
ON a.name = c.lname
WHERE (fnpctid IS NOT NULL AND lnpctid IS NOT NULL AND fnpctid = lnpctid) OR (fnpctid IS NOT NULL AND lnpctid IS NULL) OR (fnpctid IS NULL AND lnpctid IS NOT NULL)) z SET mob.npctemplateid = IFNULL(z.fnpctid, z.lnpctid) WHERE mob.mob_id = z.mob_id;
Did you take in to account that we speak also about mobs with the same name but diffrent regions and diffrent levels? Don't wanna critizise your work but somewhere in my stomach i got this warning feeling.- Set All Mob/NPCTemplate "Name" with different Case to the same Case (uses a counter to guess which version to use, do not rely on this query too much but it's usefull for the remaining actions)
- Check for NPCTemplate Attached to Mob but with different Names, If NPCT has ReplaceMobValues=1 and no other name is guessed, Mob Name is replaced, Else we try to find another name matching in Mob.Name or NPCTemplate.Name and try to get NPCT.TemplateId to correct it, If nothing is successful NPCT is detach from Mob !
- Reattach all Mob without NPCTemplateID to existing same Named mobs with valid NPCTID, or an NPCTemplate with a corresponding Name
Those cleanup and Attach/Detach ID from Mob/NPCTemplate and all table going around is needed for a script I'm writing that can create NPCTemplate for non-templated Mobs treating them as Batch of "Name" and "Region"
SELECT * FROM (SELECT COUNT(*) AS `cnt`, `TemplateId` FROM `npctemplate` GROUP BY `TemplateId` ORDER BY `TemplateId`) a WHERE `cnt` > 1
select * from mob where (x > 417850 and x < 483250) and (y < 678910 and y > 616400) and region = 1 and realm = 0 and name like "%" order by name
-- DOL gLoc to ingame /loc
-- Needed Indexes :
ALTER TABLE `zones` ADD INDEX ( `RegionID` )
ALTER TABLE `zones` ADD INDEX ( `OffsetY` )
ALTER TABLE `zones` ADD INDEX ( `OffsetX` )
ALTER TABLE `zones` ADD INDEX ( `Width` )
ALTER TABLE `zones` ADD INDEX ( `Height` )
ALTER TABLE `mob` ADD INDEX ( `X` )
ALTER TABLE `mob` ADD INDEX ( `Y` )
-- Needed View :
CREATE VIEW `inzones` AS SELECT `Zones_ID`, `ZoneID`, `RegionID`, `Name`, `OffsetY`*8192 as startY, `OffsetX`*8192 as startX, `OffsetY`*8192+`Width`*8192 as endY, `OffsetX`*8192+`Height`*8192 as endX, `Realm` FROM `zones`;
-- Select example :
SELECT * FROM `mob` LEFT JOIN inzones ON (`mob`.X BETWEEN inzones.startX AND inzones.endX) AND (`mob`.Y BETWEEN inzones.startY AND inzones.endY) AND `mob`.Region = inzones.`RegionID` WHERE inzones.name LIKE 'example'
-- New View for mob_id, mob_name, npctemplateid, mob_zone, mob_locX, mob_locY, mob_locZ
CREATE VIEW mob_loc as SELECT `mob`.`Mob_ID`, `mob`.`Name`, `mob`.`Guild`, `mob`.`NPCTemplateID`,
`inzones`.`Name` as Zone, (`mob`.`Y`-`inzones`.`startY`) as locY,
(`mob`.`X`-`inzones`.`startX`) as locX, `mob`.`Z` as locZ, `mob`.`Heading`
FROM `mob` LEFT JOIN `inzones`
ON (`mob`.`X` BETWEEN `inzones`.`startX` AND `inzones`.`endX`) AND (`mob`.`Y` BETWEEN `inzones`.`startY` AND `inzones`.`endY`) AND `mob`.`Region` = `inzones`.`RegionID`;
Only wimps use tape backup: real men just upload their important stuff on ftp, and let the rest of the world mirror it
Users browsing this forum: No registered users and 1 guest