Update with join with BigQuery

user_2320823 0
Viewed 24969

Is it possible to do an UPDATE on a table based on a JOIN with an existing table in BigQuery?

When I try this statement on the following database (https://bigquery.cloud.google.com/dataset/pfamdb:pfam31),

  UPDATE pfam31.uniprot
  SET uniprot.auto_architecture = uniprot_architecture.auto_architecture
  INNER JOIN
      pfam31.uniprot_architecture using(uniprot_acc)

I get errors relating to the INNER JOIN, with WHERE being expected instead. How should I be doing this (if it's possible)?

bigquery-standard-sql google-bigquery sql-update join
2 Answers
UPDATE `pfam31.uniprot` a
SET a.auto_architecture = b.auto_architecture
FROM `pfam31.uniprot_architecture` b
WHERE a.uniprot_acc = b.uniprot_acc
user_52219440

Please refer to the UPDATE statement syntax. There is even an example on UPDATE with JOIN. You need to use a FROM clause, and your query should be something like this:

UPDATE pfam31.uniprot
SET uniprot.auto_architecture =
  (SELECT uniprot_architecture.auto_architecture
   FROM pfam31.uniprot_architecture
   WHERE uniprot.uniprot_acc = auto_architecture.uniprot_acc);

This assumes that there is a 1:1 relationship between the uniprot_acc values in the tables. If that isn't the case, you will need to use LIMIT 1, for instance.

user_62533470
Related