Bigquery error - Subquery of type IN must have only one output column

Bigquery allows only one column as part of the subqueries. If you have more than one column, for example:

select * from dataset.employee where (empid, dept) in
(
select empid, dept from dataset.retired_employees
)

 It will throw the error: Subquery of type IN must have only one output column

The Solution:

You will need to rewrite such queries in bigquery using concat,

select * from dataset.employee where concat(empid, dept) in
(
select concat(empid, dept) from dataset.retired_employees
)

Delete using subquery with more than one column:

if you want to delete from Bigquery table, you can use the concat function like given below:

delete from dataset.employee where concat(empid, dept) in
(
select concat(empid, dept) from dataset.retired_employees
)

nVector

posted on 24 Jul 20