今天在处理删除购物车表中商品ID不存在商品表的数据时MySQL报错 1093
原SQL:
update ims_member_cart
set deleted=1
where id in (
select id from ims_member_cart
WHERE goodsid not in(SELECT id from ims_goods )
)
找了下原因是因为在update ims_member_cart表的时候,条件的子查询也包含这个表的时候,MySQL不支持
解决方法:
再嵌套一次,给查询出来的值加个别名
update ims_member_cart
set deleted=1
where id in (
select id from (
select id from ims_member_cart
WHERE goodsid not in(SELECT id from ims_goods )
) aa
)