I accidental imported suppliers from quickbooks into my RMS database. Now I have a lot of duplicates and suppliers that should not be in here. I am looking for a sql script that would delete suppliers that do not have any items associated with them. Thanks
*This post is locked for comments
Hi salemcycle,
Happy to know it helped. Please verify the answer, it may be of use for other users of this forum, in the future.
Kind regards, A.
Yes that worked great. Thank you very much.
Hi Neos.
Noup, the SupplierList table is, let say, the "link" between the Suppliers and the Items. Meaning, in the SupplierList table RMS stores the information about which Supplier what items serves and in what conditions (Price, Master Pack Quantity, Supplier Item code, etc., etc.,...). Practically, is the Items tab in the Suppliers properties form.
Hope this helps...
Best regards, A.
Hi salemcycle.
First of all, if you are planning to use any tSQL sentence against your RMS database, to delete Supplier records or anything else, please first of do it in TESTING environment!! Direct deleting can break your RMS database...
OK, now back to your problem. Knowing that Supplier "data" is stored not only in Supplier table, but also in tables as Item, PurchaseOrder and SupplierList, your tSQL script should have this included. From your question, I understand you have only "duplicated" the Suppliers, you haven't done anything with those suppliers after the import from QuickBooks. Meaning, you havent made PO against this Suppliers, nighter You have assigned Items to them, etc., etc.,... Can You confirm this, please?
Assuming that this is true, and there is notning associated to this "duplicated" suppliers, your tSQL query should be something like this:
DELETE FROM [Supplier]
WHERE [Supplier].[ID] NOT IN
(SELECT SupplierID FROM [SupplierList])
DELETE FROM [Supplier]
WHERE [Supplier].[ID] NOT IN
(SELECT SupplierID FROM [Item])
Now, before DELETING, I would run this 2 tSQL quieries, but in SELECT mode, just to confirm that the queries are working with the correct Supplier records:
SELECT * FROM [Supplier]
WHERE [Supplier].[ID] NOT IN
(SELECT SupplierID FROM [SupplierList])
SELECT * FROM [Supplier]
WHERE [Supplier].[ID] NOT IN
(SELECT SupplierID FROM [Item])
At then end, PLEASE HAVE IN MIND that this tSQL's are deleting not only the "duplicated" Suppliers, but all the Suppliers who don't have any Items assigned to them, "duplicated" with the import or not!
If You don't have much experiance with SQL, please contact your RMS Partner. Anything I can help, do not hesitate in writing to me...
BR, Antonijo
Hello,
First see that what you have in your query is what you want, use select before deleting.
I would use the Supplier ID rather then try to find associations to items as if they were imported they must be grouped sequentially by ID.
1 - Select * from Supplier ' will show you the supplier list, note the range of IDs that are created on the day of importing,
2 - Delete from Supplier where Supplier ID >= '... your start range...' and Supplier ID <= '... your end range...'
Good luck
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (