MYSQL删除某个字段多余重复记录

分手后的思念是犯贱 2022-07-12 11:08 236阅读 0赞

sells表信息

CREATE TABLE \`sells\` (  
  \`id\` int(11) NOT NULL AUTO\_INCREMENT,       \#唯一ID  
  \`name\` varchar(50) not null, \#姓名  
  \`phone\` varchar(10) not null,\#电话  
  \`project\` varchar(50) not null,\#项目名  
  \`summay\` text not null,\#简介  
  \`email\` varchar(50) not null,\#邮箱  
  \`mobile\` varchar(20) not null,\#手机  
  \`bangdin\` tinyint not null default '0',\#是否绑定  
  \`bang\_id\` int not null ,\#绑定人  
  \`level\` tinyint not null ,\#级别 a:1 b:2 c:3 d:4 d无效  
  \`notes\` text null,\#备注  
  \`notestime\` datetime null,\#备注时间  
  \`pingjiid\` int not null,\#评级人id  
  \`createtime\` datetime not null ,\#录入时间  
  \`edittime\` datetime not null,\#编辑时间  
  PRIMARY KEY (\`id\`)  
  )ENGINE=INNODB DEFAULT CHARSET=utf8;

delete \`sells\`  
from \`sells\`,  
(select \* from sells a where  exists(select 1 from sells where a.project = project AND a.id<id AND \`bangdin\` =0) AND a.\`bangdin\` =0) as ss  
where ss.project=sells.project and ss.id=sells.id

删除sells表中"未绑定记录信息" 中 "project值相同" 的 "多余重复的记录" 。

执行以下语句时：

delete sells from sells ,(select id from sells where \`bangdin\` =0 AND project in (select project from sells where \`bangdin\` =1)) AS tmp where sells.id=tmp.id

出现错误：You can't specify target table 'sells' for update in FROM clause

意思是：不能先select出同一表中的某些值，再update这个表(在同一语句中) 。mysql中不能这么用。

改为：delete sells from sells ,(select id from sells where \`bangdin\` =0 AND project in (select project from sells where \`bangdin\` =1)) AS tmp where sells.id=tmp.id

即可。功能：删除未绑定信息中project值与已绑定信息中project值相同的记录。