I have two tables oldTable
and newTable
with the contents as :
oldTable
:
key value volume
======================
1 abc 10000
2 def 5000
newTable
:
key value volume
======================
1 abc 2000
2 def 3000
3 xyz 7000
I want to create a new table which sums up the volume
s from bothe tables. i.e., the new table should contain the following contents :
joined_table
:
key value volume
======================
1 abc 12000
2 def 8000
3 xyz 7000
I tried with the following statements but to no result :
CREATE TABLE joined_table AS
SELECT key, value, volume
FROM (
SELECT IF(oldTable.key != NULL, oldTable.key, newTable.key) AS key,
IF(oldTable.value != NULL, oldTable.value, newTable.value) AS value,
IF(oldTable.volume AND newTable.volume, oldTable.volume + newTable.volume,
IF(oldTable.volume != NULL, oldTable.volume, newTable.volume)) AS volume
FROM(
SELECT oldTable.key, oldTable.value, oldTable.volume, newTable.key, newTable.value, newTable.volume
FROM newTable FULL OUTER JOIN oldTable ON newTable.key = oldTable.key
)alias
)anotherAlias;
But this throws me an error saying Query returned non-zero code: 10, cause: FAILED: Error in semantic analysis: Ambiguous column reference key
.
I tried changing the column names in the joined_table
in the above query, but it gives me the same error. Any help on how to achieve this ?
Also, is there any way I can overwrite the result to an existing table, say oldTable
instead of creating this new one ?