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值相同的记录。
今记于此,以备后用。