This query will remove all duplicate catalog items from each vendor except for those duplicate items that have been utilized on an existing Purchase Order, Receiving Document, or Bill.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

-- Select all catalogitem items into a new table
SELECT * INTO catalogitemnew FROM catalogitem
;
-- Delete any records from the new table that are for catalog items entered more than once (duplicates).
DELETE FROM catalogitemnew WHERE id IN (
SELECT id FROM catalogitem
WHERE partid IN (
SELECT partid FROM catalogitem
GROUP BY partid, vendorid
HAVING COUNT(*) > 1
))
;
-- Insert into the new table any items that are attached to existing POs, Rec Docs, or Bills
INSERT INTO catalogitemnew
SELECT * FROM catalogitem
WHERE id IN (SELECT itemid FROM vendortransdetail WHERE itemclasstypeid = 12076) 
AND id NOT IN (SELECT COALESCE(id,999999) FROM catalogitemnew)
;
-- Insert into the new table the first record for each catalog item that doesn't already exist in the new table.
INSERT INTO catalogitemnew
SELECT * FROM catalogitem 
WHERE id IN (
SELECT 
MIN(id) AS ID
FROM catalogitem
WHERE partid NOT IN (SELECT COALESCE(partid,999999) FROM catalogitemnew)
GROUP BY partid, vendorid
)
;
-- Remove all records from the existing catalog item
DELETE FROM catalogitem WHERE id > 0
;
-- Insert all the records from the new table into the catalog item.
INSERT INTO catalogitem
SELECT * FROM catalogitemnew
;
-- Finally remove the new table you created earlier as it is no longer needed.
DROP TABLE catalogitemnew

• Entered : Brandon R., Cyrious, 4/16/2013