Sqlalchemy: PostgreSQL: How to write UPDATE FROM without a subquery

[akrisanov]

Hi! I'm writing a query that updates a table column by calculating the difference between its value and column from another table. In raw SQL it looks like the following:

UPDATE
    products
SET
    quantity = products.quantity - order_items.quantity
FROM
    order_items
WHERE
    order_items.order_id = %(order_id_1) s
    AND products.code = order_items.product_code;

I've checked the SQLAlchemy documentation and found a section about the update expression:

The WHERE clause can refer to multiple tables. For databases which support this, an UPDATE FROM clause will be generated, or on MySQL, a multi-table update. The statement will fail on databases that don’t have support for multi-table update statements.

I tried to implement the query as the documentation says:

query = (
        products_table.update()
        .values(quantity=products_table.c.quantity - order_items_table.c.quantity)
        .where(
            products_table.c.code
            == select([order_items_table.c.product_code])
            .where(
                and_(
                    order_items_table.c.order_id == order_id,
                    order_items_table.c.product_code == products_table.c.code,
                )
            )
            .as_scalar()
        )
    )

But instead of a concise UPDATE ... SET ... FROM expression I got this:

from sqlalchemy.dialects import postgresql
str(query.compile(dialect=postgresql.dialect()))

UPDATE
    products
SET
    quantity =(products.quantity - order_items.quantity)
WHERE
    products.code = (
        SELECT
            order_items.product_code
        FROM
            order_items
        WHERE
            order_items.order_id = %(order_id_1) s
            AND order_items.product_code = products.code
    )

Moreover, this SQL query is not completely correct and doesn't have the required FROM statement.

Therefore, I'm trying to figure what is wrong with my query expression and if it's even possible to implement the equivalent of the raw SQL in SQLAlchemy without having the subquery. Any ideas?

Versions

• OS: MacOS
• Python: 3.8.x
• SQLAlchemy: 1.3.20
• Database: PostgreSQL 11.x

Thanks in advance!

[zzzeek]

hey there -

it seems you have misread the paragraph that you refer towards. the code example given is to support databases that do not support UPDATE FROM. For an example of UPDATE FROM please see https://docs.sqlalchemy.org/en/13/core/tutorial.html#multiple-table-updates . will add a seealso

[zzzeek]

i will remove the misleading example and replace with tutorial links

[akrisanov]

@zzzeek thanks for a quick reply! Yes, you are right. I've just written the following query:

query = (
    products_table.update()
    .values(quantity=products_table.c.quantity - order_items_table.c.quantity)
    .where(products_table.c.code == order_items_table.c.product_code)
    .where(order_items_table.c.order_id == order_id)
)

and it generates the correct SQL:

UPDATE
    products
SET
    quantity =(products.quantity - order_items.quantity)
FROM
    order_items
WHERE
    products.code = order_items.product_code
    AND order_items.order_id = %(order_id_1) s

Hooray!