mysql 子查询删除delete、修改update 使用
删除需求
表:jy_user ,jy_person
关联关系: jy_person 一对一 jy_user
问题:删除jy_person表数据,根据jy_user表activated字段条件
-- left join 删除
DELETE person from jy_person person LEFT JOIN jy_user user1 on person.user_id = user1.id WHERE user1.activated = 0;
问题:删除jy_person表数据,根据jy_person表id字段条件
-- 子查询删除
DELETE person from jy_person person ,
(SELECT id,`code`,`name` from jy_person WHERE organization_id is null AND id in (1000257,1000258,1000259,1000260,1000261,1000262)) p
WHERE p.id = person.id;;
修改需求
表:jy_user ,jy_person
关联关系: jy_person 一对一 jy_user
问题:修改jy_person表数据,根据jy_user表activated字段条件
UPDATE jy_person per,
(SELECT person.id from jy_person person LEFT JOIN jy_user user1 on person.user_id = user1.id WHERE user1.activated = 0) super
SET superior_id = null WHERE per.superior_id = super.id;
三表删除
DELETE pp from jy_person_positions pp
LEFT JOIN jy_person person on person.id = pp.persons_id
LEFT JOIN jy_user user on user.id = person.user_id WHERE user.activated = 0;