mysql处理重复数据仅保留一条记录

目的：去除（或删除）一个表里面手机号重复的数据，但是需要保留其中一个记录，换句话说，表里面手机号不为空的数据，一个手机有且只有一条记录

表结构：

CREATE TABLE `account` (

    `id` int(11) NOT NULL,

    `phone` varchar(11) DEFAULT NULL,

    PRIMARY KEY (`id`)

);

插入一些数据：

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

insert into account values('','');

查询一下现在表里面的重复情况：

select count(*)as num,phone from account where phone <> '' group by phone having num > 1;

查询结果：

现在我们要去除多余的手机号数据，直接把这个值置为空，删除同理，这里不再重复

SQL如下：

update account set phone='' where

    phone in

    (select phone from account where phone<>'' group by phone having count(id)>1)

    and

    id not in

    (select min(id) from account where phone<>'' group by phone having count(id)>1)

;

但是执行的时候会报错：

update account set phone='' where phone in (select phone from account where phone<>'' group by phone having count(id)>1)and id not in(select min(id) from account where phone<>'' group by phone having count(id)>1)    
Error Code: 1093. You can't specify target table 'account' for update in FROM clause    0.031 sec

分析：不能先select出同一表中的某些值，再update这个表(在同一语句中)

重新写SQL（取下别名）：

update account set phone='' where

    phone in

    (select a.phone from

        (select phone from account where phone<>'' group by phone having count(id)> 1) as a

    )

    and

    id not in

    (select b.id from

        (select min(id) as 'id' from account where phone<>'' group by phone having count(id)> 1) as b

    )

;

执行完成之后我们再查一遍数据的情况：

select * from account;

查询结果（每个手机号只有一条记录，其他均被置空）：

巴特西

mysql处理重复数据仅保留一条记录

最新文章

热门文章