title: SqlAlchemy UPDATE that has a FROM that is an inline SELECT rather than an explicit table
body: <p>How can I use <a href="https://docs.sqlalchemy.org/en/14/core/tutorial.html" rel="nofollow noreferrer">SQLAlchemy Core API (aka SQLAlchemy Expression Language)</a> to execute a SQL UPDATE that has a FROM that is an inline SELECT rather than an explicit table?</p>
  <p>For example, how can I express the following query using SQLAlchemy instead of sql strings?</p>
  <pre><code>UPDATE data
  SET invalid = True 
  FROM (
      SELECT data.person_id as inner_col 
      FROM data
      LEFT JOIN people
      ON people.id = data.person_id
      WHERE people.id IS NULL
  ) inner_query
  WHERE inner_query.inner_col = data.person_id
  </code></pre>
  <p>I'd like to be able to dynamically construct queries like this with variables for the table names and column names, and I'd like to use SQLAlchemy Expression Language to prevent against <a href="https://en.wikipedia.org/wiki/SQL_injection" rel="nofollow noreferrer">SQL injection attacks</a>. I also hope that SQLAlchemy will result in more maintainable code than a bunch of string concatenation (especially if the number of columns we are joining on in the inner query is a dynamic list etc.).</p>
  <hr />
  <p>For reference, here is a complete example which I'd love to have the same behavior without using <code>sqlalchemy.text</code>:</p>
  <pre><code>#!/usr/bin/env python3
  
  import sqlalchemy
  
  # create and connect to in memory database 
  engine = sqlalchemy.create_engine('sqlite://') 
  metadata_obj = sqlalchemy.MetaData()
  people = sqlalchemy.Table(
      'people', metadata_obj,
      sqlalchemy.Column('id', sqlalchemy.Integer),
      sqlalchemy.Column('name', sqlalchemy.String),
  )
  data = sqlalchemy.Table(
      'data', metadata_obj,
      sqlalchemy.Column('person_id', sqlalchemy.Integer),
      sqlalchemy.Column('data_foo', sqlalchemy.String),
      sqlalchemy.Column('invalid', sqlalchemy.Boolean),
  )
  metadata_obj.create_all(engine)
  conn = engine.connect()
  
  def create_records():
      conn.execute(people.insert().values(id=1, name='Mary'))
      conn.execute(people.insert().values(id=2, name='James'))
  
      conn.execute(data.insert().values(person_id=1, data_foo='good foo', invalid=None))
      conn.execute(data.insert().values(person_id=42, data_foo='chop suey', invalid=None))
  
  def dynamic_update(referenced_table, referenced_column, referencing_table, referencing_column):
      sql = f&quot;&quot;&quot;
  UPDATE {referencing_table}
  SET invalid = True 
  FROM (
      SELECT {referencing_table}.{referencing_column} as inner_col 
      FROM {referencing_table}
      LEFT JOIN {referenced_table}
      ON {referenced_table}.{referenced_column} = {referencing_table}.{referencing_column}
      WHERE {referenced_table}.{referenced_column} IS NULL
  ) inner_query
  WHERE inner_query.inner_col = {referencing_table}.{referencing_column}
      &quot;&quot;&quot;
      conn.execute(sqlalchemy.text(sql))
      
  
  def print_records(msg):
      print(f'{msg}:')
      print(&quot;  people:&quot;)
      for person in conn.execute(sqlalchemy.sql.select(people)):
          print('    ', person)
      print(&quot;  data:&quot;)
      for datum in conn.execute(sqlalchemy.sql.select(data)):
          print('    ', datum)
      print()
  
  create_records()
  print_records('initial values')
  dynamic_update(
      referenced_table=&quot;people&quot;,
      referenced_column=&quot;id&quot;,
      referencing_table=&quot;data&quot;,
      referencing_column=&quot;person_id&quot;,
  )
  # Instead of passing strings, I really want to do:
  # dynamic_update(people, people.c.id, data, data.c.person_id)
  print_records('after update')
  
  </code></pre>
  <p>And here is the output when run with python 3.9 and sqlalchemy 1.4.36:</p>
  <pre><code>initial values:
    people:
       (1, 'Mary')
       (2, 'James')
    data:
       (1, 'good foo', None)
       (42, 'chop suey', None)
  
  after update:
    people:
       (1, 'Mary')
       (2, 'James')
    data:
       (1, 'good foo', None)
       (42, 'chop suey', True)
  </code></pre>