MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE orders (
  orderId int,	
  userId int
);

insert into orders values
(7,	4),
(8,	4);

CREATE TABLE orderedProducts (
  orderId int,	
  productId int,
  quantity int
);

insert into orderedProducts values
(7,	22,	1),
(7,	24,	1),
(8,	22,	2),
(8,	20,	1);


CREATE TABLE products (
  productId int,	
  quantity int
);

insert into products values
(20,	1),
(22,	0),
(24,	1);

Records: 2  Duplicates: 0  Warnings: 0



Records: 4  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


select p.*,  op.quantity as orderQuantity
from orders o
JOIN orderedProducts op ON o.orderId=op.orderId 
JOIN products p ON p.productId=op.productId 
WHERE o.userId=4;


productId
quantity
orderQuantity


20
1
1

22
0
2

22
0
1

24
1
1


select p.productId, sum(distinct p.quantity) + sum(op.quantity) as newQuantity
  from products p
  inner join orderedProducts op on op.productId = p.productId
  inner join orders o on o.orderId = op.orderId
  WHERE o.userId=4
  group by p.productId


productId
newQuantity


20
2

22
3

24
2


Update products p
inner join (
  select p.productId, sum(distinct p.quantity) + sum(op.quantity) as newQuantity
  from products p
  inner join orderedProducts op on op.productId = p.productId
  inner join orders o on o.orderId = op.orderId
  WHERE o.userId=4
  group by p.productId
) s on s.productId = p.productId
set quantity = s.newQuantity

Rows matched: 3  Changed: 3  Warnings: 0


select *
from products;


productId
quantity


20
2

22
3

24
2