I have N columns: A, B, C, .. N each are decimal(8,2). I want to write a stored procedure that takes a row id and a single decimal(8,2) X.
I want it to decrement A until A reaches zero OR until it has reduced it by X. If A reaches zero and X was greater that A then I want to decrement B by X - A's original value and so on.
example:
select * from foo_table where id = 0;
| id| A | B | C |..
+---+---+---+---+
| 0 | 5 | 3 | 2 |
call my_stored_proc(0,9);
select * from foo_table where id = 0;
| id| A | B | C |..
+---+---+---+---+
| 0 | 0 | 0 | 1 |
What would be the recommended and performant way of doing this?
Given your sample data this query does the job:
UPDATE t
JOIN (
SELECT
t.id,
GREATEST(A - @a, 0) A,
@a := GREATEST(@a - A, 0),
GREATEST(B - @a, 0) B,
@a := GREATEST(@a - B, 0),
GREATEST(C - @a, 0) C,
@a := GREATEST(@a - C, 0)
FROM
t
, (SELECT @a := 9) var_init_subquery
WHERE id = 0
ORDER BY id
) sq ON t.id = sq.id
SET t.A = sq.A
, t.B = sq.B
, t.C = sq.C;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments