Update rows with multiple values from other table - BigQuery

0
votes

I have two tables in Bigquery Table A and Table B.

Table A has two columns - name(String) & value(Float). The Name columns is can contain null values.

Table B has 3 columns - start_value(Float), end_value(FLoat) and name(String). These 3 columns won't be empty at any cost.

My aim is to update Table A for the rows having name as null. The logic is basically identify the value for which name is null and then find the corresponding row in Table B where 

a.value >= b.start_value and a.value <= b.end_value

In this way, I have to update all the rows in Table A in a single query. How can I achieve this?

Note: No two rows in Table A will be same.

2
sql-updategoogle-bigquery
You have a low rate. Important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See meta.stackexchange.com/questions/5234/… for why it is important! Also important to vote on answer. Vote up answers that are helpful. ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! - Mikhail Berlyant

2 Answers

1
votes
UPDATE `project.dataset.tableA` a 
SET a.name = b.name
FROM `project.dataset.tableB` b
WHERE a.name IS NULL
AND value BETWEEN start_value AND end_value
0
votes

Here you have a code that works perfectly on my end:

UPDATE `project.dataset.tableA` a
SET a.name = (
      SELECT b.name
      FROM `project.dataset.tableB` b
      WHERE value BETWEEN start_value AND end_value)
WHERE a.name IS NULL