CODEWITHSUNDEEP
mysqlsqltemp-tables
Gizazas
Gizazas

Reputation: 65

MySQL - Add a column to Temporary Tables from another Table

I was wondering if you could add a column to a temporary table without the need to create a new one

Lets assume the following tables

Table1     |    Table2     |    Table3     |
Id  Atype  |    Id  Btype  |    Id  Ctype  |
1   A1     |    1   B1     |    1   C1     |
2   A2     |    2   B2     |    2   C2     |
3   A3     |    3   B3     |    3   C3     |

First i would like to create a temp table:

CREATE TEMPORARY TABLE IF NOT EXISTS 
  temp_table ( INDEX(id) )
AS (
  SELECT t1.id, t1.atype , t2.btype
  FROM table1 t1
  left join table2 t2 on t1.id = t2.id);

Result:
temp_table
Id  Atype  Btype
1   A1     B1
2   A2     B2
3   A3     B3

Then i would like to add Ctype also in the temp table. How can i do that? Can i join in the current temp table or do i have to create a new temp table?

The end result i am looking is 1 temp table which looks like this:

Id  Atype  Btype  Ctype
1   A1     B1     C1
2   A2     B2     C2
3   A3     B3     C3

Upvotes: 0

Views: 1655

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

Can't you just use another join?

SELECT t1.id, t1.atype, t2.btype, t3.ctype
FROM table1 t1 LEFT JOIN
     table2 t2 
     ON t1.id = t2.id LEFT JOIN
     table3 t3
     ON t1.id = t3.id

If you actually want to modify the existing table, then:

alter table temp_table add ctype varchar(255);

update temp_table tt join
       table3 t3
       on tt.id = t3.id
    set tt.ctype = t3.ctype;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could do when creating addin a 3th join()

  CREATE TEMPORARY TABLE IF NOT EXISTS 
    temp_table ( INDEX(id) )
  AS (
    SELECT t1.id, t1.atype , t2.btype, t3.Ctype
    FROM table1 t1
    left join table2 t2 on t1.id = t2.id
    left join table3 t3 on t1.id = t3.id);

Upvotes: 0

Related Questions