[SOLVED] BigQuery- Update table with Left Join and Cast function (Syntax error: Unexpected keyword CAST)

BigQuery- Update table with Left Join and Cast function (Syntax error: Unexpected keyword CAST)

In BigQuery, I'm trying to update a column 'PSB' having all null values in table t1, from another table t2 having column 'SB'. The issue is with the Key data field while doing Left Join. The data type of key in table t1 is String and in table t2 is Integer. table1 key name: 'PC' table2 key name: 'art'

I'm using the following code:

UPDATE table1 t1
SET PSB = IFNULL(sb, null)
FROM (
  SELECT cast(a.PC as INT64), b.art, b.sb
  FROM table1 a
  LEFT JOIN table2 b
  on cast(a.PC as INT64) = b.art
) t2
WHERE t1.(cast(a.PC as INT64)) = t2.art

I'm getting following error: "Syntax error: Unexpected keyword CAST"

Where am I going wrong ?

Solution

The syntax in the WHERE clause is off, and the CAST should include the entire expression. Try this version:

UPDATE table1 t1
SET PSB = IFNULL(sb, NULL)
FROM
(
    SELECT b.art, b.sb
    FROM table1 a
    LEFT JOIN table2 b
        ON CAST(a.PC AS INT64) = b.art
) t2
WHERE CAST(t1.PC AS INT64) = t2.art;

In addition, the current WHERE clause:

WHERE t1.(cast(a.PC as INT64)) = t2.art

does not make sense, since it should be correlating the two tables involved in the join. The LHS should be referring to table1 in the outer query.