{$page}

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.

code_formatsql

– 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

code

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

• Backlinks include_pagepage_componentbacklinks