Update with join with BigQuery

Question

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)?

Answer 1

UPDATE `pfam31.uniprot` a
SET a.auto_architecture = b.auto_architecture
FROM `pfam31.uniprot_architecture` b
WHERE a.uniprot_acc = b.uniprot_acc

Answer 2

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.