Missing support for UPDATE + JOIN in BigQuery dialect.

[brian-pond]

Environment

• Python 3.11 on a Debian 11 host.
• sqlalchemy version 1.4.50
• sqlalchemy-bigquery version 1.9

Steps to reproduce

Assume a BigQuery dataset has two tables: tbl_foo and tbl_bar.
You want to UPDATE values in tbl_foo, using a JOIN against tbl_bar.

update_stmt = (
    update(tbl_foo)
    .where(tbl_foo.c.id == tbl_bar.c.foo_id)
    .where(tbl_bar.c.my_group == "some group")
    .values(somefield="new-value")

engine = create_engine('bigquery://', credentials_path='/path/to/keyfile.json')
with engine.connect() as conn:
    result = conn.execute(update_stmt)
)

The compiled SQL should look something like this:

UPDATE tbl_foo
SET somefield = "new-value"
FROM tbl_bar
WHERE tbl_foo.id = tbl_bar.foo_id
AND tbl_bar.my_group = "some_group"

BigQuery does indeed support this (^) SQL syntax. I successfully ran this statement in a browser with Google Cloud and BigQuery Explorer.

However, when the Python shown above is executed, this error is thrown:

NotImplementedError: This backend does not support multiple-table criteria within UPDATE

I did some investigation. The root cause is that base SQLCompiler class has a function named update_from_clause that looks like this (GitHub).

Built-in Alchemy dialects (like PostgreSQL) overrride update_from_clause function, as shown in code here (GitHub).

Issue : The BigQuery dialect offered by sqlalchemy-bigquery does not override this function.

---

NOTE: I tried to override update_from_clause in my development environment, adding it to ../sqlalchemy_bigquery/base.py.
But this did not succeed. At runtime, Python didn't recognize my change, and continued to throw the NotImplementedError error from the original SQLCompiler class. 😕

To be fair, I don't exactly understand how sqlalchemy-bigquery is patching and overriding the vanilla sqlalchemy code (some kind of guerilla patching during import?). I tried deleting all my bytecode and __pycache__, but that didn't seem to help.