2

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 table1 SET col=val WHERE table1.key == table2.key" syntax.

In other words, I'm trying to create this sort of update query:

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'd like to do the query only once instead of twice, unless SQLite and MySQL are smart enough not to make that query twice themselves.

KimiNewt
  • 501
  • 3
  • 14

1 Answers1

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])))
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 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.

lrnzcig
  • 3,868
  • 4
  • 36
  • 50
  • It's helpful in ensuring I have no better alternative :) I ended up doing that when using SQLite and the UPDATE FROM query with MySQL (the query using select doesn't work in MySQL since it's from a temporary table and you can't use a temp table twice in one query). – KimiNewt May 13 '15 at 11:32
  • http://stackoverflow.com/questions/42613777/sqlalchemy-correlated-update-with-multiple-columns – Matthew Moisen Mar 06 '17 at 22:37