create table with select syntax in mysql

mysql sql select create-table
Alex picture Alex · Aug 15, 2012 · Viewed 9.3k times · Source

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;

does it determine which value goes into v1 and which goes into v2 by their order in the select statement? does it use the names i designate in the create table statement or does it take them from the select statement? i have used the create table XX select val from YY before but would like to know more specifically about the above syntax.

Answer

Michael Best picture Michael Best · Aug 15, 2012

You'll get a table with the columns v1, v2, a, and b.

See http://dev.mysql.com/doc/refman/5.1/en/create-table-select.html

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;

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More