update personnel p set p.status=1leftjoin classes c on p.class_id=c.class_id where c.grade_id=5;
假设经查询,grade_id=5的personnel 为201-296,可改为:
1 2 3 4 5
update personnel set status=1where id=201; update personnel set status=1where id=202; update personnel set status=1where id=203; …… update personnel set status=1where id=update personnel p set p.status=1leftjoin classes c on p.class_id=c.class_id where c.grade_id=5;
假设经查询,grade_id=5的personnel 为201-296,可改为:
1 2 3 4 5
update personnel set status=1where id=201; update personnel set status=1where id=202; update personnel set status=1where id=203; …… update personnel set status=1where id=296;
update personnel set status=1where class_id=20and status=0and create_time<update personnel set status=1where class_id=20and status=0and create_time<1571580242;
假设经查询,满足上述条件的personnelId 为220、233、234、256,可改为:
1 2 3 4
update personnel set status=1where id=220; update personnel set status=1where id=233; update personnel set status=1where id=234; update personnel set status=1where id=update personnel set status=1where id=220; update personnel set status=1where id=233; update personnel set status=1where id=234; update personnel set status=1where id=256;
select CONCAT('delete from t_bracelet_person_relation where person_id=',person_id,' and mac_id="',mac_id,'";') from t_bracelet_person_relation where mac_id in ('C9B1EC032CB3','FCBA0EB1DA09') and status=select CONCAT('delete from t_bracelet_person_relation where person_id=',person_id,' and mac_id="',mac_id,'";') from t_bracelet_person_relation where mac_id in ('C9B1EC032CB3','FCBA0EB1DA09') and status=0;
4、有条件处理
每条SQL语句必须有where条件,否则可能有问题,容易引起数据过度被处理的情况; 如:现有脚本
1
update goods set status=update goods set status=0;
update personnel p set p.status=1leftjoin classes c on p.class_id=c.class_id where c.grade_id=5; deletefrom personnel where class_id=20and status=0and create_time<1571580242;
可改为如下语句,查询后核对数据
1 2
select p.*from personnel p leftjoin classes c on p.class_id=c.class_id where c.grade_id=5; select*from personnel where class_id=20and status=0and create_time<update personnel p set p.status=1leftjoin classes c on p.class_id=c.class_id where c.grade_id=5; deletefrom personnel where class_id=20and status=0and create_time<1571580242;
可改为如下语句,查询后核对数据
1 2
select p.*from personnel p leftjoin classes c on p.class_id=c.class_id where c.grade_id=5; select*from personnel where class_id=20and status=0and create_time<1571580242;