mysql 内外连接 删除更新

1 删除

要删除的条数
select count(*) from goods a inner join goods_new b
on a.goods_id =b.goods_id – 99809
– 删除a表中b表存在的
– 正确
delete a from goods a left join goods_new b
on a.goods_id = b.goods_id
where b.goods_id is not null-- 99809

– 错误 ：只有a.goods_id is not null 一个条件或 a.goods_id is not null and b.goods_id is null 会全部删除
delete a from goods a left join goods_new b
on a.goods_id = b.goods_id
where a.goods_id is not null and b.goods_id is null-- 1211001

更新

查询要更新的条数
select count(*) from goods a inner join goods_new b
on a.goods_id =b.goods_id – 99809
and b.cate =‘2’ – 13159
– 更新 a表 在b表中有的数据
– 内连接
update goods a inner join goods_new b
on a.goods_id =b.goods_id
set a.cate=‘1’
where a.cate=‘2’-- 13159
– 左外连接
update goods a left join goods_new b
on a.goods_id =b.goods_id
set a.cate=‘1’
where a.cate=‘2’ and b.goods_id is not null – 13159
– 错误，左外连接却不带b表条件(1,a b表条件空 2、有a表条件)，将导致a表全更新
update goods a left join goods_new b
on a.goods_id =b.goods_id
set a.cate=‘1’
where a.cate=‘2’-- 73942 错误
– 2
update goods a left join goods_new b
on a.goods_id =b.goods_id
set a.cate=‘1’
where a.cate=‘2’ and a.goods_id is not null – 73942 错误
– 13159

以上仅供参考，测试库先试