SQLAlchemy Core - Multiple Table Deletes

In this chapter, we will look into the Multiple Table Deletes expression which is similar to using Multiple Table Updates function.

More than one table can be referred in WHERE clause of DELETE statement in many DBMS dialects. For PG and MySQL, “DELETE USING” syntax is used; and for SQL Server, using “DELETE FROM” expression refers to more than one table. The SQLAlchemy delete() construct supports both of these modes imppcitly, by specifying multiple tables in the WHERE clause as follows −

stmt = users.delete().
   where(users.c.id == addresses.c.id).
   where(addresses.c.email_address.startswith( xyz% ))
conn.execute(stmt)

On a PostgreSQL backend, the resulting SQL from the above statement would render as −

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s ||  %% )

If this method is used with a database that doesn’t support this behaviour, the compiler will raise NotImplementedError.