Multiple updates via a single query

[Fuuzetsu]

This continues on from #1517

Basically, it is currently impossible(?) to do arbitrary updates to multiple distinct rows with a single query. As an imaginary example to demonstrate what I mean, let's suppose we're selling tickets to some venue that allocates seats to the customers at the end of the ticket period rather than up-front. So:

• many customers reserve tickets to the venue
• time passes or tickets run out
• a piece of software allocates seats to every customer

Let's naively model it with a single table for illustration purposes

CREATE TABLE customers ( customer_id bigint PRIMARY KEY…, seat_id bigint, <more stuff here, doesn't matter> );

When customer buys a ticket, they get put on in customers with their personal data but seat_id stays NULL.

After time passes, we now have some software allocate a seat_id to every customer.

Currently with diesel, we can use AsChangeset to update a single customer, maybe it'd look something like

#[derive(AsChangeset)]
#[table_name = "customers"]
pub struct AllocateSeat {
  pub customer_id: int64,
  pub seat_id: int64
}

we should be able to now update a single customer like so:

let allocation: AllocateSeat = <magical piece of code to decide seat>;
diesel::update(customers::table).set(&allocation).execute(conn)

So far so good, but what happens if our venue has 50_000 seats? The code below doesn't compile:

let allocations: Vec<AllocateSeat> = <magical code to decide all seats>;
diesel::update(customers::table).set(&allocations).execute(conn)

So we're force to instead loop over allocations and run update for each element. This means 50_000 small queries.

At the very least postgres is able to do this in one (albeit large) query: see this StackOverflow for an example. I believe that crux is the FROM (VALUES …) where you can put all the values you want at once and then execute them with UPDATE and SET as usual.

To answer the questions from the original ticket:

What should exactly be added to diesel?

Ability to update all the rows specified in one query if the underlying DB supports it.

How would the corresponding public API look?

I'm not too in-depth to all the APIs but my naive suggestion would be to have a trait like impl<T: AsChangeset> AsChangeset for &[T] or something along these lines. Maybe we have to make use of .values, unsure.

Which backends support this SQL variant.

I only know of postgres so I can't comment on others. Quick search shows sqlite should be able to do it as well as mysql.

Where it's not easily supported, diesel could just fall back to doing a loop with one query per update. That would give consistent API at least.

Is this supported for all versions of a backend or only for specific versions

I don't know the exact versions but I think this has been a thing in mysql and postgres for many years now, definitely not a new feature.

Do other backends support the same feature with different syntax?

See above for some links.

*Are there any invariants, which should be checked at compile time?

I don't think there's anything that's not already done with the vanilla AsChangeset

[weiznich]

According to your description this can be implemented without any breaking change, which means it's not something I will work on at least till 2.0 is released (That release already took too long, so I don't want to block it on any more features).
I would likely accept a PR implementing this as long as it contains the following things:

• The corresponding change to allow updating multiple values at once. This should not break any existing API
• Updated documentation showing this feature in our API documentation
• Some test cases showing that this feature works
• Maybe a compile fail test showing that this feature is only implemented for a specific backend?

[FayCarsons]

Has this been implemented? Currently dealing w/ the same problem and am not seeing a solution in the docs, though I'm a bit of a SQL noob so I could be missing something.

[weiznich]

@FayCarsons This feature is still waiting on someone that picks it up and implements it. Your contributions are welcome here.