Use the following script, but BACKUP FIRST
--IF THE SUPPLIER TO BE UNASSUGNED IS THE PRIMARY SUPPLIER OF ANY OF THE ITEMS TO BE AFFECTED, MAKE SURE TO HAVE ANOTHER SUPPLIER ASSIGNED TO REPLACE THE PRIMARY ONCE REMOVED
--1
--This is to change the LastUpdated field of the Item table so that so u can include the intended items into 250 worksheet using "Recently Updated" option
--this will affect intended items that don't have the intended supplier as primary
update Item
set lastupdated=getdate()
where item.id in (select itemid from supplierlist inner join supplier on supplierid=supplier.id where code=@SuppCode)
and item.supplierid<>(select id from supplier where code='27')
and item.departmentid=(select id from department where code='100')
--2
--delete all records related to the supplier from SupplierList
delete supplierlist
where supplierid=(select id from supplier where code='27')
and itemid in (select item.id from item inner join department on item.departmentid=department.id where department.code='100')
--3
--to see the items still having the intended supplier as primary
select itemlookupcode,description
from item
where supplierid = (select id from supplier where code='27')
and item.departmentid=(select id from department where code='100')
--4
--change the primary supplier in the item table of the list displayed by the previous query to any existing supplier already assigned (if any) to the intended items
--items that don't have other suppliers assigned to it, will remain having
update item
set item.supplierid=supplierlist.supplierid
,lastupdated=getdate()
from supplierlist
where item.id=itemid
and item.departmentid=(select id from department where code='100')
and item.supplierid = (select id from supplier where code='27')
A Hassan
RMS Leaders
