我的餐桌推車
| cart_id | mem_id | uid | 購物車價格 |
|---|---|---|---|
| 1 | jhkwag970 | 空值 | 110 |
| 2 | jhkwag970 | 空值 | 60 |
我想要做的是一次更新這 2 行的 cart_price。但是，每個 cart_price 都會減去差值。
我努力了
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 );
我想要的結果是每個 cart_price 都被減去（110-109=1 和 60-59 =1）
| cart_id | mem_id | uid | 購物車價格 |
|---|---|---|---|
| 1 | jhkwag970 | 空值 | 1 |
| 2 | jhkwag970 | 空值 | 1 |
我知道問題發生在 set = (select ...) 子句中。SQL 顯示 1242 子查詢回傳多于 1 行的輸出。所以，我嘗試了
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 );
匹配 where 子句和 Set 子句的 cId，但回傳 B.cId is unknown 錯誤。
uj5u.com熱心網友回復：
看起來有點過于復雜。嘗試
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;
uj5u.com熱心網友回復：
解決了。我應該將 cart_id 與更新子句的 cart_id 匹配。所以，答案應該是
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 );
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/450595.html
下一篇：檢查多個列的單個值并僅回傳單個值