BigQuery, SQL update command, Error: Scalar subquery produced more than one element

Question

I am running the following command:

#standardSQL
  UPDATE `speedy-emissary-167213.pgp_orielresearch.pgp_PIK3CA_all_features_values_step_5` AS t2 
 SET 
  chr3_3308581_TGGGGT=IF(t1.PIK3CA_features="chr3_3308581_TGGGGT",1,0),
  chr3_3294651_CA=IF(t1.PIK3CA_features="chr3_3294651_CA",1,0),
  chr3_3300867_CC=IF(t1.PIK3CA_features="chr3_3300867_CC",1,0),
  chr3_3308373_ACTTGACTTG=IF(t1.PIK3CA_features="chr3_3308373_ACTTGACTTG",1,0),
  chr3_3309125__T=IF(t1.PIK3CA_features="chr3_3309125__T",1,0),
......
  chr3_3300985_TT=IF(t1.PIK3CA_features="chr3_3300985_TT",1,0),
  chr3_3308139_AC=IF(t1.PIK3CA_features="chr3_3308139_AC",1,0)
FROM
  `speedy-emissary-167213.pgp_orielresearch.pgp_PIK3CA_all_features_values_step_4_sorted_500` AS t1
WHERE
  t1.sample_id = t2.sample_id

When i run it with 500 "chr3_3308581_TGGGGT=IF(t1.PIK3CA_features="chr3_3308581_TGGGGT",1,0)," for different columns, the Scalar subquery produced more than one element is fired. when i run it with less command (by deleting part of it), the command runs successfully. what would be the easiest way to debug this query?

Thanks!

The error seems pretty straightforward. That subquery has multiple values returned when you are using it as a boolean expression. You can't evaluate an IF comparison to T/F if you have multiple inputs. — Jacob H
why multiple values are returned, these types of values: chr3_3308581_**** are unique. could you please elaborate? thanks — eilalan
I can't tell you, it's your data and I'm not clairvoyant. Run your query as a select and find out :) — Jacob H
@eilalan . . . Your code has no scalar subqueries, as far as I can tell. Either your actual code is different. you are using a view, or BQ is providing an erroneous error message. If the latter, perhaps it might occur if more than one record in the joined table matches the row being updated. — Gordon Linoff
Thank you for the comments. I added a long response below. Thanks! — eilalan

eilalan eilalan · Accepted Answer · 2017-06-20T16:27:05

missing another condition at the where clause. this worked

#standardSQL
UPDATE
  `speedy-emissary-167213.pgp_orielresearch.pgp_PIK3CA_all_features_values_step_5` AS t2
SET
  chr3_3294687_A = 1
FROM
  `speedy-emissary-167213.pgp_orielresearch.pgp_PIK3CA_all_features_values_step_4_sorted_500` AS t1
WHERE
  t1.sample_id = t2.sample_id and t1.PIK3CA_features="chr3_3294687_A"

BigQuery, SQL update command, Error: Scalar subquery produced more than one element

1 Answers