Update Supplier ID By Deparment ID SQL Query

Test on a test system before doing the following on your live system.

Always backup your live system before performing any updates.

Perform the following SQL queries in Store Operations Administrator

=====================================================================Step 1. Find the DepartmentID by running this query.

SELECT * FROM Department

Step 2. Find the SupplierID by running this query.

SELECT ID, SupplierName FROM Supplier

Step 3. Run the following query to check you have the correct Items. (Substitute the DepartmentID and SupplierID with yours)

SELECT * FROM Item WHERE DepartmentID = 99 AND SupplierID = 999

Step 4. Double check the results from Step 3 are the correct items

Step 5. Run the following queries in order. (Substitute the DepartmentID and SupplierID with yours)

UPDATE Item SET Item.SupplierID = 99 WHERE DepartmentID = 999

DELETE FROM SupplierList FROM SupplierList LEFT JOIN Item ON SupplierList.ItemID = Item.ID WHERE Item.DepartmentID = 99

INSERT INTO SupplierList (ItemID, SupplierID) SELECT ItemID, SupplierID FROM Item WHERE DepartmentID = 99 and SupplierID = 999

Step 6. Check your items from in Store Operations Manager

Note: All the suppliers information on each item (Reorder No., Min. Order, MPQ, Cost) will be blank ====================================================================

Sorry All,

Slight typo in the last statment before step 6

Should be

INSERT INTO SupplierList (ItemID, SupplierID) SELECT ID, SupplierID FROM Item WHERE DepartmentID = 99 and SupplierID = 999

I am looking for a SQL Query, Where I can add my SupplierID = 21 (Supplier ID for Wharehouse Supplier ! in my database) as a primary Supplier for all items in database.

Existing Supplier Can still still exist in Database as secondry supplier.

Thanks > Sorry All,

Here are a few SQL Queries to do with cleaning up your Supplier information that may help you.

I would run these 2 queries first to ensure you have clean data.

==========================================Þlete records in the SupplierList where the Item no longer exists ==========================================DELETE FROM SupplierList FROM SupplierList LEFT JOIN Item ON SupplierList.ItemID = Item.ID WHERE (((Item.ID) Is Null));

============================================Þlete records in the SupplierList where the Supplier no longer exists ============================================DELETE FROM SupplierList FROM SupplierList RIGHT JOIN SupplierList ON Supplier.ID SupplierList.SupplierID WHERE (((Supplier.ID) Is Null));

===================================The following 2 queries should do what you requested ==================================UPDATE Item SET Item.SupplierID = 21 WHERE SupplierID 21

INSERT INTO SupplierList (Item.ItemID, Item.SupplierID) SELECT Item.ID as ItemID, Item.SupplierID FROM Item LEFT JOIN SupplierList ON (Item.ID = SupplierList.ItemID) AND (Item.SupplierID = SupplierList.SupplierID) WHERE (((Item.SupplierID) Is Not Null AND Not (Item.SupplierID)=0) AND ((SupplierList.ItemID) Is Null) AND ((SupplierList.SupplierID) Is Null));

======================================================================================Finally run the following query to find duplicate suppliers for an Item and then manually remove them in Store Operations Manager =====================================================================================SELECT Item.ItemLookupCode, Item.Description, SupplierList.ItemID, SupplierList.SupplierID, SupplierList.MinimumOrder, SupplierList.ID, SupplierList.Cost, SupplierList.ReorderNumber, SupplierList.MasterPackQuantity, SupplierList.TaxRate FROM SupplierList INNER JOIN Item ON SupplierList.ItemID = Item.ID WHERE (((SupplierList.ItemID) In (SELECT [ItemID] FROM [SupplierList] As Tmp GROUP BY [ItemID],[SupplierID] HAVING Count(*)>1 And [SupplierID] [SupplierList].[SupplierID]))) ORDER BY SupplierList.ItemID, SupplierList.SupplierID;