I need to UPDATE a boolean type column in a table (table_bq
) in Big Query using an External Table (external_table
). The problem is: the column I need to update will be updated only if another column from the table_bq
matches with the column from the external_table
, for that I need to use the LIKE statement.
I wrote this query below.
UPDATE table_bq
SET table_bq.column1 = True
WHERE table_bq.column2 = (SELECT
table_bq.column2
FROM
table_bq
INNER JOIN
external_table ON UPPER(table_bq.column2) LIKE CONCAT('%',UPPER(external_table.column),'%'));
But I'm getting the following error: Scalar subquery produced more than one element.
Does anybody know how I can fix the query?
CodePudding user response:
I have looked at the documentation and it states that you cannot update an external table but it does not say that you cannot use an external table in a normal multi table update so I would try -
UPDATE table_bq
INNER JOIN external_table
ON UPPER(table_bq.column2) LIKE CONCAT('%',UPPER(external_table.column),'%'))
SET table_bq.column1 = True
but if that is not allowed you will have to change =
to IN
for the subquery as it returns multiple rows -
WHERE table_bq.column2 IN (SELECT