CODEWITHSUNDEEP
mysql
Steven
Steven

Reputation: 19425

How can I insert single occurrence of a duplicate name into another table?

I need to insert one of each duplicate name from one table into another.
And duplicates only.

The following query lists all duplicate names and the count:

select name, count(name) as cnt
from my_table
group by name
having cnt > 1
order by name

How can I insert one of each occurrence into another table?

Update

My tables are not identical. My new table only has the following rows:

id (auto increment)  
name (varchar)

Upvotes: 0

Views: 96

Answers (1)

dani herrera
dani herrera

Reputation: 51685

First create table.

Then insert data to new table:

insert into new_table 
select name, count(name) as cnt
from my_table
group by name
having count(name) > 1
order by name

Consult 12.2.5.1. INSERT ... SELECT Syntax for detailed information.

EDITED

You can specify columns name or order, for example:

insert into new_table (column1, column2)
select name, count(name) as cnt
...

For your table, you need to create another field to store cnt:

alter table `new_table` add column cnt int;

insert into new_table (name, cnt)
select name, count(name) as cnt
...

Upvotes: 3

Related Questions