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?
This question is related to
mysql
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;
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
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];
Source: Stackoverflow.com