MySQL create table with select, alias column names

Question

Is there any way to use a create table with select statement where column names in conflict (or all) are aliased?

CREATE TABLE newTable
SELECT a.*, b.*
FROM tblA a
JOIN tblB b
  ON a.id = b.cid

The issue is that tblA and tblB have a few columns with the same name, so I get a "duplicate column name" error on the create. I'm trying to avoid listing all the fields in the table, so I either need to selectively exclude some columns or apply and "auto alias" to the column names.

I think that you're going to have to list out the columns, and manually assign aliases... — Michael Fredrickson, Mar 27 '12 at 18:34
It depends. Do you want values from both tables for columns with duplicate names? Or do you just want (for example) the MAX value? — liquorvicar, Mar 27 '12 at 18:40

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

You can use the information_schema table to selectively exclude columns in a select statement. See the top answer here.

edited May 23 '17 at 11:56

Community

1
1

answered Mar 27 '12 at 18:39

Bobby W

865
6
8

MySQL create table with select, alias column names

1 Answers1