CODEWITHSUNDEEP
sqlgoogle-bigquerysql-update
Marhis
Marhis

Reputation: 29

How can I update a column in one table based on a match with another table in BigQuery?

SQL in BigQuery - UPDATE must match at most one source row for each target row

I'm trying to write data to the column "vertical" in tableA using data in tableB with the same column name, based on a match between the columns "query" from tableA and "keyword" from tableB. Problem is: tableA has multiple rows with the same match (sample data provided below).

Error message I'm getting is

UPDATE/MERGE must match at most one source row for each target row

This is the query I'm currently running:

UPDATE `tableA` AS tA
SET tA.vertical = tB.vertical
FROM `tableB` AS tB
WHERE tA.query = tB.keyword;

And those are the samples for the tableA and tableB.

tableA

query unique_key vertical
abc 123456 null
abc 789abc null
abc defghi null
def 5l5p5p null
def qwertz null
def m43222 null

tableB

keyword vertical
abc planet
def earth

And this is how I was expecting the tableA to be after running the query:

Updated tableA

query unique_key vertical
abc 123456 planet
abc 789abc planet
abc defghi planet
def 5l5p5p earth
def qwertz earth
def m43222 earth

Any support is greatly appreciated.

Upvotes: 1

Views: 3880

Answers (1)

lemon
lemon

Reputation: 15502

That error occurs when the relationship between the two tables is 1:n, that is, "tA.query" is matched with multiple "tB.keyword". In order to solve this problem, you need to restrict the relationship to 1:1,

  • either by selecting the DISTINCT couples of your table B
UPDATE `tableA` AS tA
SET tA.vertical = tB.vertical
FROM (SELECT DISTINCT vertical, query FROM `tableB`) AS tB
WHERE tA.query = tB.keyword;
  • or by selecting the MIN/MAX of your keyword, given the assumption that such operation would be correct for your output
UPDATE `tableA` AS tA
SET tA.vertical = tB.vertical
FROM (SELECT vertical, MIN(query) FROM `tableB` GROUP BY vertical) AS tB
WHERE tA.query = tB.keyword;

Upvotes: 0

Related Questions