Using 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.

Using SQLAlchemys 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)

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

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)

The following code depicts the resulting UPDATE query −

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

SQLite dialect however doesnt support multiple-table criteria within UPDATE and shows following error −

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