*This post is locked for comments
Hi A,
Thank you for this very detailed answer. I will be using your query
Margaret
Thanks very much for your suggestion
Kind regards
Margaret
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
You'd need to run a DELETE query, but you have to hit both the supplierlist and item tables; if you can, bite the bullet and do it through the gooey (delete the supplier in the SUPPLIER tab of the item; add the correct supplier there as needed, too)
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,802 Super User 2024 Season 2
Martin Dráb 229,133 Most Valuable Professional
nmaenpaa 101,154