Is it possible to execute multiple UPDATES at once?

[fafhrd91]

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

could anyone point me to a api doc or example?

[sgrif]

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

[fafhrd91]

something like:

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

[sgrif]

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)?;

[fafhrd91]

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

[sgrif]

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

[fafhrd91]

Thanks.

[thomasetter]

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

[mjanda]

It seems like it's using update ... from syntax and building that update sql manually.

https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/frameworks/Rust/actix/src/db_pg.rs#L180

Nice, didn't even know postgresql can do that.