mysql 修改重复的数据库,mysql修改重复数据只保留一条数据不修改

一般我们对要入库的数据进行整理时需要对重复数据进行处理,一种是直接删除多余的重复数据只保留一条，还有一种是标记多余的重复数据为删除状态只保留一条，这样能最大限度保留原始入库数据记录。

以下为第二种修改重复数据只保留一条不修改：

首先创建测试表并插入测试数据：create table Userinfo

(id bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',

Name VARCHAR(30) NOT NULL DEFAULT '' COMMENT '姓名',

AreaCode VARCHAR(30) NOT NULL DEFAULT '' COMMENT '区域编码',

Phone VARCHAR(15) NOT NULL DEFAULT '' COMMENT '手机号',

IsDelete BIT(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',

PRIMARY KEY (`id`)

);

一、查找手机号相同的重复数据select id,Phone from Userinfo where isdelete=0 group by Phone having count(0)>1

二.查找同区域下手机号相同的重复数据select * from Userinfo a

WHERE (a.Phone, a.AreaCode) IN (SELECT Phone,AreaCode

FROM Userinfo where isdelete =0

GROUP BY Phone,AreaCode HAVING

count(*) > 1)

正式开始：修改手机号相同的重复数据字段IsDelete=1 只保留一条状态不改变

第一步:找出重复记录数据：

select * from Userinfo a

WHERE (a.Phone, a.AreaCode) IN (SELECT Phone,AreaCode

FROM Userinfo where isdelete =0

GROUP BY Phone,AreaCode HAVING

count(*) > 1)

第二步:找出重复记录中要保留的记录(可根据id排序取id最大的一条：

SELECT max(id) FROM Userinfo

GROUP BY Phone,AreaCode HAVING count(*) > 1)

第三步:找出需要修改IsDelete=1 的数据

select * from Userinfo a

WHERE (a.Phone, a.AreaCode) IN (SELECT Phone,AreaCode

FROM Userinfo where isdelete =0

GROUP BY Phone,AreaCode HAVING

count(*) > 1)

and a.id NOT IN (SELECT

SELECT max(id) FROM Userinfo

GROUP BY Phone,AreaCode HAVING count(*) > 1)

第四步:修改要修改IsDelete=1 的重复数据

update Userinfo set isdelete =1 where id in(select * from (

select id from Userinfo a

WHERE (a.Phone, a.AreaCode) IN (SELECT Phone,AreaCode

FROM Userinfo where isdelete =0

GROUP BY Phone,AreaCode HAVING

count(*) > 1)

and a.id NOT IN (SELECT

SELECT max(id) FROM Userinfo

GROUP BY Phone,AreaCode HAVING count(*) > 1)

) m )

至此 修改手机号相同的重复数据字段IsDelete=1 只保留一条状态不改变的工作已经完成。尤其注意不加一层查询的话直接套update语句会报错，加一层查询相当于查询结果在一个临时集合中，不会触发锁

另一个方法就是把要更改IsDelete=1状态的数据id写入一个表中insert into tableA (delid)

select id as delid from Userinfo a

WHERE (a.Phone, a.AreaCode) IN (SELECT Phone,AreaCode

FROM Userinfo where isdelete =0

GROUP BY Phone,AreaCode HAVING

count(*) > 1)

and a.id NOT IN (SELECT

SELECT max(id) FROM Userinfo

GROUP BY Phone,AreaCode HAVING count(*) > 1)

然后修改

update Userinfo set isdelete =1 where id in(select delid from tableA)

相对比较第二种方法适合大量数据操作