因为系统bug在对所在店铺的会员进行屏蔽的时候没有进行查重操作,导致在屏蔽表中出现了重复的数据,所以需要删除此表中姓名重复的数据,并保留其中的一条。模拟数据如下图:
第一条sql :
delete from t_black_list where sellerId = 120055122 and names in (
select names from t_black_list where sellerId = 120055122 and types = 4 group by names having count(names) > 1
)
and id not in (
select id from t_black_list where sellerId = 120055122 and types = 4 group by names having count(names) > 1
)
遇到了问题:
遇到You can’t specify target table ‘表名’ for update in FROM clause这样的错误,它的意思是说,不能先select出同一表中的某些值,再update这个表(在同一语句中),即不能依据某字段值做判断再来更新某字段的值。
解决:(中间表过度)
select * from t_black_list where sellerId = 120055122 and names in (
SELECT a.names from (select names from t_black_list where sellerId = 120055122 and types = 4 group by names having count(names) > 1) a
)
and id not in (
SELECT b.id from (select id from t_black_list where sellerId = 120055122 and types = 4 group by names having count(names) > 1) b
)
注:
mysql group by 会选择id最小的那个