Our partner added about 2000 general industry suppliers when they created our database. Problem is, we only use about 200 of them and it makes it difficult having to always go through all these useless suppliers. Is there a query we can run to find/delete suppliers that do not have any products attached to them
Cheers
Hi Jetspeed, You can run this query to see which suppliers has products (Go SO Admin> Go to File and then Connect to Database > Then click on Query > New) select * from supplier where id in (select supplierid from supplierlist)
You can now run the following query where suppliers does not have products select * from supplier where id not in (select supplierid from supplierlist) now wanted to delete above query result supplier
delete from supplier where id in (select supplierid from supplierlist)
This will work .please rate.
"jetspeed" wrote:
Thanks for that.
I've noticed that a lot of people say to be careful when running delete queries because it doesn't perform the checks RMS does. Will this be Ok as the suppliers don't have any items?
delete from supplier where id NOT in (select supplierid from supplierlist)
Cheers
Glad you caught the missing NOT there...
There are a couple of other places that you might want to check for Supplier references before deleting, but since you have said that these suppliers were never used, it's unlikely that you will find them...
select itemlookupcode, supplierid from item where supplierid not in (select supplierid from supplier list)
select PONumber, supplierid from purchaseOrder where supplierid not in (select supplierid from supplier list)
All Update Insert and Delete queries are dangerous, mainly because it's very easy to make a tiny little mistake like that and do very bad things to your data.
Always make sure you have a current backup of your data before running any such query.
If you don't understand what the query is doing, don't run it.
If you get a twinge in your guts before clicking the run button, don't run it - get someone you trust and who knows SQL (like your reseller/partner hopefully) to help.
Glenn Adams Tiber Creek C> Thanks for that.
HI jet, you can do so but this will deletes the suppliers who has no products attach to it. I am writing one more query to be safe side which will tell you before deleting such make sure no PO is also raised by this supplier to main some more links:
select * from supplier where not exists (select supplierid from supplierlist where supplier.id=supplierid) and id in (select supplierid from purchaseorder)
What happen some time is that RMS does not directly add the items to supplierlist table however there are already PO raised to your supplier the above query return you the correct results and making you sure that you are doing correct work.
Hi you not rate my previous hint.
"jetspeed" wrote:
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.