CODEWITHSUNDEEP
mysql
Kim
Kim

Reputation: 2156

mysql - error with creating temporary table from resultset

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.

Upvotes: 2

Views: 1106

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270609

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.

Upvotes: 4

Mchl
Mchl

Reputation: 62369

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.

Upvotes: 2

Related Questions