mysql - error with creating temporary table from resultset

Question

i want to create a temporary table on the fly from a result set so that i can run some queries against this resultset.

Here is my sql:

CREATE TABLE temp_table_1 AS (SELECT * FROM A LEFT OUTER JOIN B ON A.id = B.id WHERE B.id IS null);


SELECT QUA.id, QUA.name, QUA.address, QUA.acc, QUA.module, QUA.tag
FROM QUA,temp_table_1
WHERE 
QUA.name = temp_table_1.name AND 
QUA.acc = temp_table_1.acc AND
QUA.tag = temp_table_1.tag

When i run the first query to create the temp table, I get the error message:

'Duplicate Colum Name 'id''

Thanks a lot for your help.

Michael Berkowski · Accepted Answer · 2012-02-07 14:54:02Z

4

You're getting both columns A.id and B.id without aliases to differentiate them. Be specific about the columns you select:

CREATE TABLE temp_table_1 AS (
  SELECT 
    A.id AS aid
    A.othercol AS a_othercol,
    B.id AS bid,
    B.othercol AS b_othercol
  FROM A LEFT OUTER JOIN B ON A.id = B.id WHERE B.id IS null);

It's unwise to use SELECT * because you can't always guarantee the order that the columns will return in, if something changed in the original CREATE TABLE statement, for example. Also, as you've discovered here, similarly named columns will cause problems in many circumstances.

edited Feb 7, 2012 at 14:54

answered Feb 7, 2012 at 14:48

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kim Over a year ago

Thanks a lot for these details

Mchl · Accepted Answer · 2012-02-07 14:48:03Z

2

THat's because you do SELECT *. The resultset contains all columns from both tables, so if both of them have a column called 'id' it wil be duplicated.

answered Feb 7, 2012 at 14:48

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Collectives™ on Stack Overflow

mysql - error with creating temporary table from resultset

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related