delete rows with duplicate column data

-- MAKE A BACKUP OF npc_vendor BEFORE RUNNING

SET @FilteredVendorEntry = 600009;

-- Add displayid column
ALTER TABLE npc_vendor ADD COLUMN displayid INT UNSIGNED NULL;

-- Set correct displays
UPDATE npc_vendor SET displayid = (SELECT displayid FROM item_template WHERE item_template.entry = npc_vendor.item);

-- Filter out duplicates leaving the smallest item entry
DELETE a FROM npc_vendor a, npc_vendor b WHERE a.item > b.item AND a.displayid = b.displayid AND a.entry = @FilteredVendorEntry AND b.entry = @FilteredVendorEntry;

-- Drop the displayid column
ALTER TABLE npc_vendor DROP COLUMN displayid;