MySQL update one table with the values of another table - Could I use it in bigquery?

1
votes

In MySQL, I could use 

update TableA, TableB
set TableA.field1 = TableB.field1
where TableA.id = TableB.id

TableA
id field1 field2
1  hello  hi

TableB
id field1 field2
1   world earth

After run the query, TableA value will be updated to

TableA
id field1 field
1  world  hi

In BigQuery, I cannot do the same. It seems that I have to loop through TableB, update TableA for each matching row. BigQuery is with huge data. Looping through TableB for each matching row, do an update query is not going to perform well.

Or for performance reason, in bigquery, I could

  • select field1, field2 from TableA where id not in Select id from TableB (if id not in TableB, take TableA values)
  • select TableB.field1 as field1, TableA.field2 as field2 from TableA, TableB where TableA.id = TableB.id (if Id in TableB, then use TableB.field1 value)
  • Union the above two results

Any suggestions?

1
google-bigquery

1 Answers

2
votes

In BigQuery, I cannot do the same ...

You sure can!!! With DML of BigQuery Standard SQL

UPDATE `project.dataset.TableA` AS TableA
SET TableA.field1 = TableB.field1
FROM `project.dataset.TableB` AS TableB
WHERE TableA.id = TableB.id