I need to take all unique data from a mysql table and put it into a new table

Question

Hi, I have a database table (mysql 5) something like this example data:

id,name,description

1,example,test description

2,example,different desc

3,example*2*,another desc

4,example*3*,blah blah blah

(note that #1 and #2 have the same name, 3 and 4 are unique.

what i want to do is make a new table, with just this data:

1,example,test description

3,example2,another desc

4,example3,blah blah blah

ie, make sure column #2 is unique.

how can i do this easily? i have no preference as to what ones it doesnt keep

Answer 1

Try this:

INSERT INTO
    new_table(`id`, `name`, `description`)
SELECT `id`, `name`, `description` FROM old_table GROUP BY `name`

If you want your ID's to be re-generated, you can drop the id columns from that query, provided that your ID column is auto_increment.

Another way is to put an UNIQUE constraint on name field in new_table and use the IGNORE keyword:

INSERT IGNORE INTO
    new_table(`id`, `name`, `description`)
SELECT `id`, `name`, `description` FROM old_table