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

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 doesn’t support multiple-table criteria within UPDATE and shows following error −

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