MariaDB 10.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table orders(id int not null auto_increment primary key, payment varchar(64), type varchar(64), order_status varchar(64), amount float);
insert into orders(type, order_status, amount) values(2, 1, 50),(2, 1, 50),(5, 1, 50),(5, 1, 50),(5, 1, 50),(5, 1, 50),(5, 1, 50);
UPDATE 
  orders AS o 
  JOIN (
    SELECT 
      id, 
      payment, 
      type, 
      order_status, 
      amount, 
      @total := SUM(amount) 
    FROM 
      orders 
    WHERE 
      type = 5 
      AND order_status BETWEEN 1 
      AND 2 FOR 
    UPDATE 
      ) AS n ON o.id = n.id 
SET 
  o.payment = 3
select * from orders;
select @total;


id
payment
type
order_status
amount


1
null
2
1
50

2
null
2
1
50

3
3
5
1
50

4
null
5
1
50

5
null
5
1
50

6
null
5
1
50

7
null
5
1
50




@total


250

id	payment	type	order_status	amount
1	null	2	1	50
2	null	2	1	50
3	3	5	1	50
4	null	5	1	50
5	null	5	1	50
6	null	5	1	50
7	null	5	1	50