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
4 of 5 based on 8298 votesComments