John Davidson

php - MySql - Update the status of rows using FIFO logic

0 comments
Message:


I've the below mysql table where I want to update multiple rows of approval_status field with 1 if the quantity passed is more than the qty_req value using MySql or PHP.


Requisition table:












































id part_id qty_req approval_status
1 16 20 0
2 17 30 0
3 16 40 0
4 17 50 0
5 17 60 0


My Answer:
You can achieve this by using a FIFO (First In First Out) logic to update the approval_status field in the Requisition table. Here is an example query in MySQL to update the approval_status field based on the condition you mentioned:

sql
UPDATE Requisition
SET approval_status = 1
WHERE id IN (
SELECT id
FROM (
SELECT id, @running_total := @running_total + qty_req AS running_total
FROM Requisition, (SELECT @running_total := 0) AS rt
ORDER BY id
) AS t
WHERE running_total <= (SELECT SUM(qty) FROM (SELECT * FROM Requisition) AS temp WHERE temp.id <= t.id)
);


This query will update the approval_status field to 1 for all rows where the running total of qty_req is less than or equal to the sum of qty for all previous rows with the same part_id.

You can execute this query in your MySQL database to update the approval_status field as per your requirements.

Rate this post

3 of 5 based on 3818 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us