Diesel: Is it possible to execute multiple UPDATES at once?

Created on 30 Jan 2018 · 9Comments · Source: diesel-rs/diesel

I couldn't find any information on possibility to execute multiple updates.

could anyone point me to a api doc or example?

Source

fafhrd91

Most helpful comment

something like:

UPDATE posts SET field='1' WHERE ..;
UPDATE posts SET field='2' WHERE ..;
UPDATE posts SET field='2' WHERE ..;

fafhrd91 on 30 Jan 2018

👍2

All 9 comments

Can you expand on what you mean by "execute multiple updates"? Can you provide the SQL query you'd like to construct?

sgrif on 30 Jan 2018

something like:

UPDATE posts SET field='1' WHERE ..;
UPDATE posts SET field='2' WHERE ..;
UPDATE posts SET field='2' WHERE ..;

fafhrd91 on 30 Jan 2018

👍2

You just need to call execute on multiple update statements. e.g.

update(posts).set(field.eq("1")).filter(...).execute(&conn)?;
update(posts).set(field.eq("2")).filter(...).execute(&conn)?;

sgrif on 30 Jan 2018

would it execute each update separately?

some background, I use diesel for TechEmpower framework benchmarks and it performs very well.
but result for "db update" benchmark is very weak. I see other frameworks do batch updates

https://github.com/TechEmpower/FrameworkBenchmarks/tree/master/frameworks/Rust/actix

fafhrd91 on 30 Jan 2018

I haven't seen much real world usage that needs to execute multiple unrelated update statements in a single round trip. If you want to optimize for that benchmark, you can pass a SQL string to connection.batch_execute (this is how migrations are run).

sgrif on 30 Jan 2018

Thanks.

fafhrd91 on 30 Jan 2018

Another approach which also works with prepared statements is to chain the updates with WITH to create a single statement containing several updates:
https://dba.stackexchange.com/questions/171123/use-same-postgres-with-in-multiple-queries