删除重复数据
有这么一张表
name | age | address |
---|---|---|
tom | 22 | 广州 |
scott | 18 | 广州 |
scott | 18 | 美国 |
alice | 22 | 美国 |
tom | 22 | 广州 |
marry | 23 | 香港 |
tom | 22 | 广州 |
- 要删掉重复数据
delete from tb_test
where name in(
select name
from tb_test
group by name,age,address
having count(*)>1
)
- 删除重复数据,但要保留一条
create table tb_tmp as select distinct name,age,address from tb_test;
truncate table tb_test;
insert into tb_test (name,age,address) select name,age,address from tb_tmp;
drop table tb_tmp;