Create Table from Select problem...

[yves030]

hi experts,

i want to dynamically create a new table where the columns should be taken from a "source-db" and the rows should be filled by a select-statement. (i know about the problems with indices, value-conversion etc. that doesn´t matter..)
can someone explain, why this statement results in an "duplicate column-name-error":

CREATE TABLE source.tmp SELECT * FROM source.t1, target.t1 WHERE source.t1.c1=target.t1.c1

while this statement (which imho does exactly the same) works?:

CREATE TABLE source.tmp SELECT source.t1.* from source.t1 LEFT JOIN target.t1 ON source.t1.c1=target.t1.c1 WHERE source.t1.c1=target.t1.c1

i stepped over this by trying to redesign the statements according to the mysql-reference-manual recommendations. is this a bug or am i missing something? i think it´s got something to do with the "SELECT * FROM source.t1, target.t1" because i select from two tables (which have, btw., identical schemes) ... but the select itself works fine... but if i insert the CREATE TABLE before it fails...

thanks,
yves

p.s.
mysql-version is: 4.0.16
OS is: linux

 

[hoinz]

Hi,

your two queries are not doing exactly the same:

SELECT * FROM source.t1, target.t1 WHERE ...
will select all columns of source.t1 AND all columns of target.t1.
If column names are the same, you will get two columns with the same name in your select.

SELECT source.t1.* from source.t1 LEFT JOIN ...
will select all columns of source.t1 only.
And that's less columns than your first select.

hope this helps

 

[yves030]

you got it! i was so blind... thanx a lot!

yves