问一下如上的两张表，问一下怎么样实现如下的功能：
update goods sg set sg.stock=sg.stock-(select soi.number from order_item soi where soi.order_no = "SX20151209165533144" ) where sg.id in (select soi.goods_id from shop_order_item soi where soi.order_no ="SX20151209165533144" )
本人上面写的SQL有问题
根据订单编号，来更新商品表里面的库存数目。两张表通过商品编号关联。
orderItem表里面存的有订单编号，以及订单的商品的数量，订单支付成功之后，商品库存数要减去这个orderItem里面这个”数量” 谢谢。
解决方案
30
update goods sg inner join (
select goods_id ,number
from shop_order_item
where order_no =”SX20151209165533144″
) b on sg.id =b.goods_id
update sg.stock=sg.stock-b.number
select goods_id ,number
from shop_order_item
where order_no =”SX20151209165533144″
) b on sg.id =b.goods_id
update sg.stock=sg.stock-b.number
20
update goods sg inner join (
select goods_id ,number
from shop_order_item
where order_no =”SX20151209165533144″
) b on sg.id =b.goods_id
set sg.stock=sg.stock-b.number
select goods_id ,number
from shop_order_item
where order_no =”SX20151209165533144″
) b on sg.id =b.goods_id
set sg.stock=sg.stock-b.number
15
update goods sg,order_item soi, set sg.stock=sg.stock-soi.number where soi.order_no ="SX20151209165533144" and sg.id=soi.goods_id
15
错了，多了个逗号，应该是
update goods sg,order_item soi set sg.stock=sg.stock-soi.number where soi.order_no ="SX20151209165533144" and sg.id=soi.goods_id
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明关于关联update的sql的写法！