CODEWITHSUNDEEP
mysqlsqlselectcreate-table
Alex
Alex

Reputation: 19803

Create table with SELECT syntax in MySQL

I would like to know how MySQL interprets the CREATE TABLE syntax:

If I write:

CREATE TABLE tbl1 (
    `v1` int,
    `v2` int
     CONSTRAINT idx PRIMARY KEY (v1)
)
SELECT a, b FROM tbl2;

I have used the CREATE TABLE XX SELECT val FROM YY before but would like to know more specifically about the above syntax.

Upvotes: 6

Views: 13251

Answers (1)

Michael Best
Michael Best

Reputation: 16688

With your current solution you'll get a table with the columns v1, v2, a, and b.

To see how it is done properly, see the "CREATE TABLE ... SELECT Statement" chapter in the official MySQL documentation.

So if you just want v1 and v2 with an index on v1, do it like this:

CREATE TABLE tbl1 (PRIMARY KEY (v1))
SELECT a v1,
       b v2
FROM tbl2;

Upvotes: 11

Related Questions