======  ======



===== Explanation of SQL =====



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.



 



===== Risk of Data Corruption if Run Improperly =====

**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**.



 



===== SQL =====



<code sql>
-- 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>



===== Version Information =====
  * Entered : Brandon R., Cyrious, 4/16/2013