google-bigquery 使用另一个表更新表

Question

嗨，我在 BigQuery 中有两个表:

表A:消费者ID，消费者分割

表B:消费者ID

我想要做的就是用“找到”更新 TableA.consumerSegment，其中 TableA.consumerID=TableB.consumerID

我正在使用此语句，但作为返回，我收到一个错误:

UPDATE `MyTableLongNameA` AS TableA

SET TableA.consumerSegment = "Found"

FROM `MyTableLongNameB` AS TableB

WHERE TableA.consumerID = TableB.ConsumerID

错误是:

Scalar subquery produced more than one element

任何帮助表示赞赏。

谢谢

Answer 1

这表明 TableB 中存在多个匹配项获取来自 TableA 的 key .来自 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.

试试这个:

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