Purchase inventory table: the code name of the unit price amount
Inventory storage table: the code name of the unit price amount
Sales outbound table: code name of the unit price amount
In allocating outbound table: the code name of the unit price amount
Dish deficient outbound table: the code name of the unit price amount
Inventory list: encoding name unit price of inventory quantity inventory amount
Inventory=inventory + inventory - sales dispatch - allocating outbound - dish deficient outbound
Normal without error, every inbound outbound inventory is correct, but for some reason, the inventory may be inaccurate
Just need to check inventory
Before check inventory is inventory=inventory + inventory - sales dispatch - allocating outbound - dish deficient outbound to each commodity circulation processing
The small number of cases are possible for commodity wait
But now the number is too much, this kind of method to deal with its
So I ask you, what good method or with multiple tables to UPDATEL joint?
CodePudding user response:
UPDATE the inventory table a,
(
The SELECT encoding, SUM number (quantity) FROM
(
The number of SELECT encoding, FROM purchasing warehousing table
UNION ALL
SELECT encoding, number FROM inventory storage table
UNION ALL
SELECT encoding, the number - FROM sales outbound table
UNION ALL
SELECT encoding, the number - the FROM allocating outbound table
UNION ALL
SELECT encoding, the number - the FROM dish deficient outbound table
) t GROUP BY t. coding
) the SET b a. quantity=b. quantity
WHERE a. Code=b. coding AND a. number!=b. number;
CodePudding user response: