Inserting the values from another table while creating new table

Question

Here is my FIDDLE.

I am trying to import the data from old table to new table. In old table there are many no of repetitions. In new table i am able to insert only DISTINCT emails. I am unable to Insert the name as same. Here is my code.

CREATE TABLE table_old(name VARCHAR(255), email VARCHAR(255));

INSERT INTO table_old (name, email) VALUES ('tom', '[email protected]'),
                                           ('peter', '[email protected]'),
                                           ('hitler', '[email protected]'),
                                           ('haasan', '[email protected]'),
                                           ('arun', '[email protected]'),
                                           ('tom', '[email protected]'),
                                           ('peter', '[email protected]'),
                                           ('hitler', '[email protected]'),
                                           ('haasan', '[email protected]'),
                                           ('arun', '[email protected]');

CREATE TABLE table_new AS (SELECT DISTINCT email FROM table_old );

So please give me idea how to insert the names into table_new with respect to the email column name.

Answer 1

I think this is what you're after:

CREATE TABLE table_new AS (SELECT name, email FROM table_old GROUP BY name, email);