How to Remove Duplicate Gameobjects from the Database

Before typing out the rest of this post, I will note that this is about removing gameobjects which have the same displayId as one another from the database. Not everyone will have a good reason to do this, so if you’re not very experienced in editing the database, then do not attempt to remove the duplicate gameobjects. If you know of any additional information, notice an error, etc.. just comment and I’ll do my best to keep this post updated. In this post I will assume that you know enough to be able to execute SQL queries on your database.

Just in case anyone wants to know what I’m running:

• Windows 8, x64 — Home
• Debian 7, x64 — Server
• TrinityCore — Latest version as of 20/June/2015
• WoTLK 3.3.5a

After using a slightly slower version of the query below, I’ve come up with an improved form that will do all of the work that had previously needed to be done manually. Simply execute the set of queries below as many times as you want and for any objects, where two or more objects use the same displayId, all duplicate objects will be deleted and all reference to those objects in the gameobjects table will be set to use the remaining gameobject.

Don’t worry about objects disappearing in the game world, the query replaces all objects that use the same displayId with the object whose entry and displayId you are entering into the set of queries. This is really only useful for a highly customized server as any normal server would find a lot of the in-game objects broken or not working properly after deleting the duplicates as many of them have slightly different sets of data which may leave chairs unusable unless manually fixed, objects unusable unless manually fixed, etc…

SELECT @entry := entry, name, @display := displayId FROM gameobject_template WHERE displayId = (SELECT displayId FROM gameobject_template GROUP BY displayId ORDER BY COUNT(*) DESC LIMIT 1);
UPDATE gameobject JOIN gameobject_template ON gameobject.id = gameobject_template.entry SET gameobject.id = @entry WHERE gameobject_template.entry != @entry AND gameobject_template.displayId = @display;
DELETE FROM gameobject_template WHERE displayId = @display AND entry != @entry;

You will need to run the three queries above a few thousand times to eliminate all of the duplicate objects in the database. I simple copy-pasted the three queries over and over and over until I felt there were enough and then ran them all at once. It took a few minutes, but all of the duplicate objects were removed.