CREATE TABLE LIKE A1 as A2

Question

I want to create a new table with properties of an old table and without duplicates  I want to do something like this   CREATE TABLE New Users  LIKE Old Users   AS  SELECT   FROM Old Users GROUP BY ID      But the above is not working   Can anybody modify it to work

User · Accepted Answer

Your attempt wasn t that bad  You have to do it with LIKE  yes   In the manual it says      Use LIKE to create an empty table based on the definition of another   table  including any column attributes and indexes defined in the   original table    So you do   CREATE TABLE New Users  LIKE Old Users    Then you insert with  INSERT INTO New Users SELECT   FROM Old Users GROUP BY ID    But you can not do it in one statement

User · Answer

Based on http   dev mysql com doc refman 5 0 en create-table-select html  What about   Create Table New Users Select   from Old Users Where 1 2    and if that doesn t work  just select a row and truncate after creation   Create table New Users select   from Old Users Limit 1  Truncate Table New Users    EDIT   I noticed your comment below about needing indexes  etc  Try   show create table old users   copy the output ddl statement into a text editor and change the table name to new users  run the new query insert into new users id name     select id name     form old users group by id    That should do it  It appears that you are doing this to get rid of duplicates  In which case you may want to put a unique index on id  if it s a primary key  this should already be in place  You can either    make primary key alter table new users add primary key  id    make unique create unique index idx new users id uniq on new users  id

User · Answer

You can use below query to create table and insert distinct values into this table   Select Distinct Column1  Column2  Column3 into New Users from Old Users

User · Answer

CREATE TABLE new table LIKE old table   or u can use this  CREATE TABLE new table as SELECT   FROM old table WHERE 1 GROUP BY  column to remove duplicates by

User · Answer

For MySQL  you can do it like this   CREATE TABLE New Users   SELECT   FROM Old Users group by ID

[mysql] CREATE TABLE LIKE A1 as A2

Examples related to mysql