alembic: use subquery for update statement in migration

Question

I'm using alembic to manage my database migrations. In my current migration I need also to populate a column based on a SELECT statement (basically copying a column from a different table).

With plain SQL I can do:

UPDATE foo_table
SET bar_id=
    (SELECT bar_table.id FROM bar_table
        WHERE bar_table.foo_id = foo_table.id); 

However can't figure out how to do that with alembic:

execute(
    foo_table.update().\
        values({
            u'bar_id': ???
        })
) 

I tried to use plain SQLAlchemy expressions for the '???':

select([bar_table.columns['id']], 
       bar_table.columns[u'foo_id'] == foo_table.columns[u'id'])

But that only generates bad SQL and a ProgrammingError during execution:

'UPDATE foo_table SET ' {}

Answer 1

Actually it works exactly as I described above.

My problem was that the table definition for 'foo_table' in my alembic script did not include the 'bar_id' column so SQLALchemy did not use that to generate the SQL...