CODEWITHSUNDEEP
mysql
Doyley
Doyley

Reputation: 321

ALTER TABLE LIKE

Is it possible to use the LIKE statement on ALTER TABLE similar to CREATE TABLE in MySQL?

Eg. 'CREATE TABLE db.tbl1 LIKE db.tbl2'

This clones a database table's structure. I want to alter an existing table with the same columns but to pick up the primary keys of another table.

I was thinking of something like 'ALTER TABLE db.tbl1 LIKE db.tbl2' but this throws back an error.

Any ideas?

Thanks

Upvotes: 2

Views: 5490

Answers (3)

kraftb
kraftb

Reputation: 655

I required a similar thing and settled to use the following procedure:

ALTER TABLE tbl1 RENAME tbl1_old;
CREATE TABLE tbl1 LIKE tbl2;
INSERT INTO tbl1 SELECT * FROM tbl1_old;
DROP TABLE tbl1_old;

Altough this is not a single statement it should do the job. Only problem could be different index settings (UNIQUE, etc.) which cause errors when doing the "INSERT INTO" of the original table contents.

Upvotes: 3

Mchl
Mchl

Reputation: 62395

Everything ALTER TABLE can do is explained here.

As you can see importing indexes from another table is not mentioned. You could probably do that with some clever information_schema querying, but I don't think it would be worth the cost.

Upvotes: 1

ascanio
ascanio

Reputation: 1526

It seems you can't.

Upvotes: 0

Related Questions