Update a Joined Table with SQLAlchemy Core

Question

I have a MySQL db with tables set up like this:

Table1      Table2
------      ------
  id          id, fk to Table1.id
  name        name

I want to update Table1 and set Table1.id = Table2.id if Table1.name = Table2.name. Or, in SQL:

UPDATE table1 t1 
INNER JOIN table2 t2 
ON t1.name = t2.name
SET t1.id = t2.id;

How can I accomplish an equivalent statement using the SQLAlchemy Core API?

I can call table1.join(table2, table1.c.name == table2.c.name) to create the join, but how can I update this joined table?

Answer 1

upd = table1.update()\
    .values(id=table2.c.id)\
    .where(table1.c.name == table2.c.name)

should do it, but if you really have all those foreign keys, you might get errors doing such updates.