Inner Join syntax error in Alembic execute

Question

I have a "DevAssociation" table with columns prod_id and dev_id where dev_id is a fk from DevRun, I would like to move prod_id to DevRun table, so I created a new column in DevRun called prod_id. Then I am doing the following SQL command to move the prod_id data over. I am doing this from alembic in Python but I feel like where it's failing is more generally related to sql.

    conn.execute('UPDATE "DevRun"\
         INNER JOIN "DevAssociation" \
             ON "DevAssociation".dev_id= "DevRun".id \
                 SET prod_id="DevAssociation".prod_id')

I get the following error

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "INNER"
LINE 1: UPDATE "DevRun" INNER JOIN "DevAssociation" O...
                             ^

[SQL: UPDATE "DevRun" INNER JOIN "DevAssociation" ON "DevAssociation".dev_id= "DevRun".id SET prod_id="DevAssociation".prod_id]

Any idea why? This is on postgresql.

Answer 1

update in postgresql based on another table looks like this:

UPDATE "DevRun"
SET prod_id="DevAssociation".prod_id
FROM "DevAssociation"
WHERE "DevAssociation".dev_id= "DevRun".id