删除重复行 error 1093

删除重复行和error 1093

解决1：直接删除（拥有自动增长的cust_id列）

delete from `customers` where cust_id not in

     (select cust_id from (select min(cust_id) as cust_id from `customers` GROUP BYcust_name)as a);

 

解决2：删除重建

create table tmp   (select a.* from customers a,

     (select min(cust_id) as cust_id from `customers` group by cust_name)  b where a.cust_id=b.cust_id);

或

create table tmp2 select min(cust_id) as cust_id,

cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email

from`customers` GROUP BY cust_name;

或

create table tmp1 select distinct * from customers_copy;    //没有自动增长id列，采用这种方法

 

drop table customers;

rename table tmp to customers;

 

我在创建过程中出现了一个如下报错：

1093错误：You can'tspecify target table 'perf_linux_t' for update in FROM clause 

解决：将子查询变为孙子查询，再嵌套一个查询，一定要定义别名

delete from `customers` where cust_id not in

     (select cust_id from (select min(cust_id) as cust_id from `customers` GROUP BY cust_name) as a); 

 

附：

//查看表中的重复行信息

select cust_name from (SELECT cust_name,count(*) FROM `customers` GROUP BY cust_name having count(*)>1);

 

//若没有自动增长的id列，可以添加

ALTER TABLE `perf_linux_t` ADD COLUMN `auto_id`INT NOT NULL AUTO_INCREMENT primary key;

 

如果大家还有更好的方法，望高手指点。

我在创建过程中出现了一个如下报错：

1093错误：You can'tspecify target table 'perf_linux_t' for update in FROM clause 

解决：将子查询变为孙子查询，再嵌套一个查询，一定要定义别名

delete from `customers` where cust_id not in

     (select cust_id from (select min(cust_id) as cust_id from `customers` GROUP BY cust_name) as a); 

 

附：

//查看表中的重复行信息

select cust_name from (SELECT cust_name,count(*) FROM `customers` GROUP BY cust_name having count(*)>1);

 

//若没有自动增长的id列，可以添加

ALTER TABLE `perf_linux_t` ADD COLUMN `auto_id`INT NOT NULL AUTO_INCREMENT primary key;