Updating a table from another table with multiple columns in sqlalchemy

Question

I want to update multiple columns of one table to match the other multiple columns of another table in SQLAlchemy. I use SQLite when testing it, so I can not use the syntax `UPDATE table1 SET col = val WHERE table1.key == table2.key.

In other words, I am trying to create such an update request:

UPDATE table1 SET col1 = (SELECT col1 FROM table2 WHERE table2.key == table1.key), col2 = (SELECT col2 FROM table2 WHERE table2.key == table1.key) 

In SQLAlchemy:

 select_query1 = select([table2.c.col1]).where(table1.c.key == table2.c.key) select_query2 = select([table2.c.col2]).where(table1.c.key == table2.c.key) session.execute(table.update().values(col1=select_query1, col2=select_query2)) 

Only I would like to make a query only once, and not twice, if SQLite and MySQL are not smart enough not to make this query twice.

Answer 1

I do not think you can. So this is actually not an answer, but a comment too long.

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

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

and then you can use the with_only_columns() method, 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]))) UPDATE table SET a=(SELECT tweet.id FROM tweet WHERE tweet.id IS NOT NULL), b=(SELECT tweet.user_id FROM tweet WHERE tweet.id IS NOT NULL) 

But, as you can see from the update statement, you will effectively make two choices. (Sorry, I did not fully adapt the output to your example, but I'm sure you understand this idea).

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