title: How to write UPDATE FROM without a subquery in SQLAlchemy for PostgreSQL
body: <p>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:</p>
  <pre class="lang-sql prettyprint-override"><code>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;
  </code></pre>
  <p>I've checked the SQLAlchemy documentation and found a <a href="https://docs.sqlalchemy.org/en/13/core/dml.html#sqlalchemy.sql.expression.update.params.whereclause" rel="nofollow noreferrer">section</a> about the <code>update</code> expression:</p>
  <blockquote>
  <p>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.</p>
  </blockquote>
  <p>I tried to implement the query as the documentation says:</p>
  <pre class="lang-py prettyprint-override"><code>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()
          )
      )
  </code></pre>
  <p>But instead of a concise <code>UPDATE ... SET ... FROM</code> expression I got this:</p>
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy.dialects import postgresql
  str(query.compile(dialect=postgresql.dialect()))
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>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
      )
  </code></pre>
  <p>Moreover, this SQL query is not completely correct and doesn't have the required <code>FROM</code> statement.</p>
  <p>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?</p>
  <p><strong>Versions</strong></p>
  <ul>
  <li>OS: MacOS</li>
  <li>Python: 3.8.x</li>
  <li>SQLAlchemy: 1.3.20</li>
  <li>Database: PostgreSQL 11.x</li>
  </ul>
  <p><strong>Thanks in advance!</strong></p>