When the quantity in the ITEM database does not match the sum in the RELATION (Item + Location) database, this script will fix it.
MAKE SURE TO BACKUP FIRST!!! Accessing the database directly can lead to serious or even fatal unintended consequences! This is only recommended for VERY knowledgeable BACKTRACK users.
Open SQL Management Studio, connect to the correct database, and run this scrip.
For every row in the ITEM databaseCalculate the sum of QTY in the RELATION database where item_no in RELATION = item_no in ITEMReplace the ITEM_QTY in ITEM with the sum
Note that zlnw0001 is the name of the database, Item_NO is 7 character long. Make necessary adjustments before running this. DON’T FORGET TO BACKUP THE DATA BEFORE RUNNING THIS SCRIPT.
declare @item char(7)declare @counter intselect @item = min(item.item_no) from [zlnw0001].[dbo].[ITEM]while @item is not nullbeginset @counter = (select sum(qty) from common.dbo.relation where c_item_no = @item)update [zlnw0001].[dbo].[ITEM]set ITEM_QTY = @counter where item_no = @itemselect @item = min(item.item_no) from [zlnw0001].[dbo].[ITEM] where item_no > @itemend