Bigquery SQL: Convert table values using a key-value table

Question

I have two tables. The first table contains the key-value data:

   Answer_Code |Fruit
   1           |Apple
   2           |Orange
   3           |Pear
   4           |Watermelon

The second table contains the data collected

   Participant|Fruit
   Aaa        |1
   Bbb        |2
   Ccc        |3

How can I join these tables so that the Fruit column in the second table will be replaced with the Fruit values in the first table?

   Participant|Fruit
   Aaa        |Apple
   Bbb        |Orange
   Ccc        |Watermelon

I have tried the following:

SELECT T1.*, T2.*,
FROM [Table1] T1, 
JOIN [Table2] T2 ON T1.Fruit = T2.Fruit,
LIMIT 10;

I get the following error:

ON clause must be AND of = comparisons of one field name from each table, with all field names prefixed with table name. Consider using Standard SQL .google.com/bigquery/docs/reference/standard-sql/), which allows non-equality JOINs and comparisons involving expressions and residual predicates.

Further I cannot get UPDATE, SET to work on bigquery.

Use StandardSql! That is the simplest solution. – Gordon Linoff May 20 '18 at 11:05 — Gordon Linoff, May 20 '18 at 11:05

score 1 · Accepted Answer · answered May 20 '18 at 17:10

Your ON clause is incorrect
Instead of ON T1.Fruit = T2.Fruit you should have ON T1.Answer_Code = T2.Fruit
Also you have few extra commas in your query

Anyway, your query should look like below

SELECT T2.Participant, T1.Fruit
FROM [Table1] T1 
RIGHT JOIN [Table2] T2 ON T1.Answer_Code = T2.Fruit
LIMIT 10

It is also recommended to migrate to BigQuery Standard SQL - Standard SQL is the preferred SQL dialect for querying data stored in BigQuery and also has multiple advantages including DML so you can get UPDATE, SET to work on BigQuery

Bigquery SQL: Convert table values using a key-value table

1 Answers1