CREATE TABLE LIKE A1 as A2

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.

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);

For MySQL, you can do it like this:

CREATE TABLE New_Users   SELECT * FROM Old_Users group by ID;

CREATE TABLE LIKE A1 as A2

Related

Recent Posts