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
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

Bobby W Bobby W · Accepted Answer · 2012-03-27T18:39:18

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

MySQL create table with select, alias column names

1 Answers