24 Jul 20 · npack · #Bigquery ·   Bookmark   ×

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
)

npack

posted on 24 Jul 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds