因为一些测试原因,导致医生表里出现了两个医生ID相同的数据,也就是所谓的脏数据,那么如何把多余的那条删除掉呢?
一、首先筛选出条数大于1条的:
select doctor_id,count(doctor_id) from `doctor_info` where `scale_group_id`="31" group by doctor_id having count(doctor_id)>1
二、决定删除其中的那一条?max/min
1、max
select max(id) as id from doctor_group_permission where `scale_group_id`="24120868141551631" group by doctor_id having count(doctor_id)>1
2、min
select min(id) as id from doctor_group_permission where `scale_group_id`="24120868141551631" group by doctor_id having count(doctor_id)>1
三、数据筛选出来了,直接delete 报错了
delete from doctor_info where id in(select max(id) as id from doctor_info where `scale_group_id`="31" group by doctor_id having count(doctor_id)>1)
四、错误说明
不能先select出同一表中的某些值,再update这个表(在同一语句中),即不能依据某字段值做判断再来更新某字段的值。
借用临时表来处理一下,前端时间写过一个update的,链接在此
五、正确delete操作 借用临时表a
delete from doctor_info where id in(select a.id from(select max(id) as id from doctor_info where `scale_group_id`="31" group by doctor_id having count(doctor_id)>1) a)