Updating A Table From Another Table With Multiple Columns In Sqlalchemy

I want to update multiple columns of one table according to other multiple columns of another table in SQLAlchemy. I'm using SQLite when testing it, so I can't use the `UPDATE tabl

Solution 1:

I don't think you can. Thus, this is not really an answer, but it is far too long for a comment.

You can easily compose your query with 2 columns (I guess you already knew that):

select_query = select([table2.c.col1, table2.c.col2]).where(table1.c.key == table2.c.key)

and afterwards you can use the method with_only_columns(), see api:

In[52]: print(table.update().values(col1 = select_query.with_only_columns([table2.c.col1]), col2 = select_query.with_only_columns([table2.c.col2])))
UPDATEtableSET a=(SELECT tweet.id 
FROM tweet 
WHERE tweet.id ISNOTNULL), b=(SELECT tweet.user_id 
FROM tweet 
WHERE tweet.id ISNOTNULL)

But as you see from the update statement, you will be effectivelly doing two selects. (Sorry I did not adapt the output completely to your example, but I'm sure you get the idea).

I'm not sure whether, as you say, MySQL will be smart enough to make it one query only. I guess so. Hope it helps anyway.