[masticism]

I did this mainly to get rid of the duplicate NPC's in paineel (they were starting to annoy me). They may be there for a reason but until the "proper" update gets finished, this will at least get rid of them. No gaurantees though. There is most likely intentional duplicates that are being removed as well...


Adapted from molly's SQL:

1) First, verify that there are duplicates that we want removed:

SELECT 
  `spawn2`.`x`,
  `spawn2`.`y`,
  `spawn2`.`z`,
  `spawn2`.`zone`,
  `npc_types`.`name`,
  count(`spawn2`.`id`) AS `SpawnCount`
FROM
  `npc_types`
  INNER JOIN `spawnentry` ON (`npc_types`.`id` = `spawnentry`.`npcID`)
  INNER JOIN `spawn2` ON (`spawnentry`.`spawngroupID` = `spawn2`.`spawngroupID`)
WHERE
   spawn2.pathgrid = 0
GROUP BY
  `spawn2`.`x`,
  `spawn2`.`y`,
  `spawn2`.`z`,
  `spawn2`.`zone`
HAVING
  (`SpawnCount` = 2)
ORDER BY
  `spawn2`.`zone`,
  `spawn2`.`x`,
  `spawn2`.`y`,
  `spawn2`.`z`

2) Next, for the deletion, we just reuse pieces from above:

SELECT CONCAT('delete from spawn2 WHERE id = ', max(spawn2.id),';DELETE FROM spawnentry WHERE spawngroupID = ', max(spawn2.spawngroupID),';') Query
FROM
  `npc_types`
  INNER JOIN `spawnentry` ON (`npc_types`.`id` = `spawnentry`.`npcID`)
  INNER JOIN `spawn2` ON (`spawnentry`.`spawngroupID` = `spawn2`.`spawngroupID`)
WHERE
   spawn2.pathgrid = 0
GROUP BY
  `spawn2`.`x`,
  `spawn2`.`y`,
  `spawn2`.`z`,
  `spawn2`.`zone`
HAVING
  COUNT(spawn2.id) =2
ORDER BY
  `spawn2`.`zone`,
  `spawn2`.`x`,
  `spawn2`.`y`,
  `spawn2`.`z`

3) This will give several SQL statements that will delete the highest ID of the duplicate entry.

Again no gaurantees on this, use at your own risk etc.

Thanks to molly for the great inspiration for this!!!