In MySQL I have two tables:
t_balances
Y,
t_detail
I want to mark in the FLG field the records of each COD as long as the sum of AMOUNT contains the value of the TOTAL field .
For example for the COD = 11
In this case, the sum of the first 4 records of COD = 11 gives 67 and this value contains 50, which is the TOTAL value of the table t_balances .
Tables are in DB Fiddle
I used a process from a similar question
SET @codigo:=0;
SET @t:=0;
UPDATE t_detalle JOIN t_saldos USING(cod)
JOIN (
SELECT id,cod,
IF(@codigo!=cod,@t:=0,@t),
if(@codigo!=cod,@codigo:=cod,@codigo),
@t:=@t+importe acumulado
FROM t_detalle
ORDER BY cod,id
) c1 USING(id)
set flg=1
WHERE acumulado<=total; <-- NO ME SIRVE Si es >= marca desde valor mayor, y TAMPOCO si es <= marca los menores pero no marca el que contiene la suma.
But I think the WHERE is where things get complicated.
UPDATE: Initially the FLG field has the value 0. And I want to mark with 1 those that meet the condition.
You could add a column where you control if the accumulated in
@t
is less thantotal
.Example:
Result:
Then you can use
esMenor = 1
to know which ones to mark.Solution:
Result:
Consider that the accumulated is never really zero, at least it is worth the amount of the current record.
With this query you can determine the value of
flg
:And use
flg
to do the JOIN in the UPDATE: