My table carts

What I am trying to do is update those 2 rows' cart_price at once. But, each cart_price are subtracted with difference value.

I have tried

 update carts
 set cart_price = (select salePrice from(
 select (cart_price-round((cart_price / (select (sum(cart_price)) from carts where mem_id = 'jhkwag970' and cart_imp_uid is null )) * 168)) as salePrice
 from carts
 where mem_id = 'jhkwag970' and cart_imp_uid is null
) A)
where cart_id in
(select cId from(select cart_id as cId
 from carts
 where mem_id = 'jhkwag970' and cart_imp_uid is null) B ); 

the result that I wanted is each cart_price is being subtracted (110-109=1 and 60-59 =1)

I know that problem occurs in set = (select ...) clause. SQL show output that 1242 subquery returns more than 1 rows. So, I tried

update carts
 set cart_price = (select salePrice from(
 select cart_id, (cart_price-round((cart_price / (select (sum(cart_price)) from carts where mem_id = 'jhkwag970' and cart_imp_uid is null )) * 168)) as salePrice
 from carts
 where mem_id = 'jhkwag970' and cart_imp_uid is null
) A where A.cart_id = B.cId)
where cart_id in
(select cId from(select cart_id as cId
 from carts
 where mem_id = 'jhkwag970' and cart_imp_uid is null) B );

to Match cId of where clause and Set Clause, but returns B.cId is unknown error.

CodePudding user response：

Looks a bit overcomplicated. Try

update carts c
join (
  select cart_id, cart_price - round(cart_price /(
      select sum(c.cart_price) from carts c where c.mem_id = 'jhkwag970' and c.uid is null) * 168) v
  from carts
  where mem_id = 'jhkwag970' and uid is null
  ) A on c.cart_id = A.cart_id
set c.cart_price = A.v; 

CodePudding user response：

Solved it. I should match the cart_id with the update clause's cart_id. So, answer should be

 update carts c
 set cart_price = (select salePrice from(
 select cart_id, (cart_price-round((cart_price / (select (sum(cart_price)) from carts where mem_id = 'jhkwag970' and cart_imp_uid is null )) * 168)) as salePrice
 from carts 
 where mem_id = 'jhkwag970' and cart_imp_uid is null
) A where A.cart_id = c.cart_id)
where cart_id in
(select cId from(select cart_id as cId
 from carts
 where mem_id = 'jhkwag970' and cart_imp_uid is null) B );