Home | SQL Script to Fix QTY Mismatch Between ITEM and RELATION Database – BACKTRACK Instruction Article
Print or Email this article

SQL Script to Fix QTY Mismatch Between ITEM and RELATION Database – BACKTRACK Instruction Article

Summary

When the quantity in the ITEM database does not match the sum in the RELATION (Item + Location) database, this script will fix it.

Instructions

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.

The logic is:
For every row in the ITEM database
    Calculate the sum of QTY in the RELATION database where item_no in RELATION = item_no in ITEM
    Replace 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 int
 
select @item = min(item.item_no) from [zlnw0001].[dbo].[ITEM]
 
while @item is not null
 begin
set @counter = (select sum(qty) from common.dbo.relation where c_item_no = @item)
update [zlnw0001].[dbo].[ITEM]
 
set ITEM_QTY = @counter where item_no = @item 
select @item = min(item.item_no) from [zlnw0001].[dbo].[ITEM] where item_no > @item
 
end

Author

Dave Klement

Was this article helpful?

Related Articles