使用SQLAlchemy Core更新联接表 [英] Update a Joined Table with SQLAlchemy Core

问题描述

我有一个MySQL数据库，其表设置如下:

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

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

我想更新Table1并将Table1.id = Table2.id设置为Table1.name = Table2.name.或者，在SQL中:

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;

如何使用SQLAlchemy Core API完成等效语句?

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

我可以调用table1.join(table2, table1.c.name == table2.c.name)来创建联接，但是如何更新此联接表?

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

推荐答案

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.

这篇关于使用SQLAlchemy Core更新联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！