CODEWITHSUNDEEP
google-bigquery
mjdxb
mjdxb

Reputation: 85

google-bigquery update table using another table

Hi I have two tables in BigQuery:

TableA : consumerID,consumerSegment

TableB : consumerID

All I want to do is to update TableA.consumerSegment with "found" where TableA.consumerID=TableB.consumerID

I am using this statement but in return, I am getting an error:

UPDATE `MyTableLongNameA` AS TableA

SET TableA.consumerSegment = "Found"

FROM `MyTableLongNameB` AS TableB

WHERE TableA.consumerID = TableB.ConsumerID

The error is:

Scalar subquery produced more than one element

Any help is appreciated.

Thanks

Upvotes: 6

Views: 11298

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33765

This indicates that there is more than one match in TableB for a key from TableA. From the documentation:

If a row in the table to be updated joins with exactly one row from the FROM clause, then the row is updated.

If a row in the table to be updated joins with more than one row from FROM clause, then the query generates a runtime error.

Try this instead:

UPDATE `MyTableLongNameA` AS TableA
SET TableA.consumerSegment = "Found"
WHERE EXISTS (
  SELECT 1
  FROM `MyTableLongNameB` AS TableB
  WHERE TableA.consumerID = TableB.ConsumerID
)

Upvotes: 4

Related Questions