Sqlalchemy 简明教程

Using Multiple Table Updates

在上一章中，我们讨论了如何使用多张表。因此，我们在本章中进一步学习 multiple table updates 。

In the previous chapter, we have discussed about how to use multiple tables. So we proceed a step further and learn multiple table updates in this chapter.

使用 SQLAlchemy 的表对象，可以在 update() 方法的 WHERE 子句中指定多个表。PostgreSQL 和 Microsoft SQL Server 支持引用多张表的 UPDATE 语句。这实现了 “UPDATE FROM” 语法，该语法一次更新一张表。但是，可以在 WHERE 子句的附加“FROM”子句中直接引用其他表。以下代码行清楚地解释了 multiple table updates 的概念。

Using SQLAlchemy’s table object, more than one table can be specified in WHERE clause of update() method. The PostgreSQL and Microsoft SQL Server support UPDATE statements that refer to multiple tables. This implements “UPDATE FROM” syntax, which updates one table at a time. However, additional tables can be referenced in an additional “FROM” clause in the WHERE clause directly. The following lines of codes explain the concept of multiple table updates clearly.

stmt = students.update().\
values({
   students.c.name:'xyz',
   addresses.c.email_add:'abc@xyz.com'
}).\
where(students.c.id == addresses.c.id)

update 对象等效于以下 UPDATE 查询：

The update object is equivalent to the following UPDATE query −

UPDATE students
SET email_add = :addresses_email_add, name = :name
FROM addresses
WHERE students.id = addresses.id

在 MySQL方言中，多个表格可以使用逗号分隔后嵌入到一个 UPDATE 语句中，如下所示：

As far as MySQL dialect is concerned, multiple tables can be embedded into a single UPDATE statement separated by a comma as given below −

stmt = students.update().\
   values(name = 'xyz').\
   where(students.c.id == addresses.c.id)

以下代码描绘了所生成的 UPDATE 查询：

The following code depicts the resulting UPDATE query −

'UPDATE students SET name = :name
FROM addresses
WHERE students.id = addresses.id'

SQLite 方言不支持 UPDATE 中的多表格条件，并显示以下错误：

SQLite dialect however doesn’t support multiple-table criteria within UPDATE and shows following error −

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