4 ms·

Yes, ideally in a single UPDATE statement.

adrianmonk 9y ago
I'm not sure how to do that because a single UPDATE statement only has one WHERE clause. You'd have to come up with an expression to put in the SET part that gives the right values for different rows. I guess you can always swap pairs of rows with subtraction, like swapping row 5 and 7 would be: update t set user_order = 5 + (7 - user_order) where id in (123, 456) Maybe you could use case statements to make an enumerated function in the more general case of updating multiple rows, but that's a bit ugly.
- Beanis 9y ago
  Postgres bulk update: http://sqlfiddle.com/#!17/b3d19/1 http://sqlfiddle.com/#!17/b3d19/1 You can do something similar in MySQL with temp tables. Bulk update through upsert (slightly different than a bulk update, since it can create rows too) MySQL: http://sqlfiddle.com/#!9/a6f050/1 http://sqlfiddle.com/#!9/a6f050/1 Postgres: http://sqlfiddle.com/#!17/a8367/1 http://sqlfiddle.com/#!17/a8367/1
  - adrianmonk 9y ago
    OK, so SQL extensions, so just the usual reasons to do bulk changes. I thought they might have mentioned it here because there was something unique to say about it in the context of this problem.
  - imtringued 9y ago
    And preferably you use the postgres specific "unnest(array1, array2, ...)" function which turns array parameters passed into the query into a temporary table that you can query. UPDATE example SET position = CAST(temp.position AS INTEGER) FROM unnest(:ids, :positions) AS temp (id, position) WHERE example.id = CAST(temp.id AS INTEGER);
- dllthomas 9y ago
  idk if this is what the parent had in mind, but this seems straightforward enough: update t set user_order = case when id = 123 then 500 else user_order + 1 end where id = 123 or user_order >= 500;
- etatoby 9y ago
  This is just off the top of my head, but to move an item from position P to Q, you would do something like this: update items set pos = case when pos = P then Q when P < Q and pos between P and Q then pos - 1 when Q < P and pos between Q and P then pos + 1 else pos end Plus a where clause to limit the shuffling to the actual list being reordered, since you would keep all users' lists in the same table.
chris_wot 9y ago
If you do them in a transaction with the correct isolation level, it will appear as a single atomic update.

I'm not sure how to do that because a single UPDATE statement only has one WHERE clause. You'd have to come up with an expression to put in the SET part that gives the right values for different rows. I guess you can always swap pairs of rows with subtraction, like swapping row 5 and 7 would be: update t set user_order = 5 + (7 - user_order) where id in (123, 456) Maybe you could use case statements to make an enumerated function in the more general case of updating multiple rows, but that's a bit ugly.

Beanis 9y ago
Postgres bulk update: http://sqlfiddle.com/#!17/b3d19/1 http://sqlfiddle.com/#!17/b3d19/1 You can do something similar in MySQL with temp tables. Bulk update through upsert (slightly different than a bulk update, since it can create rows too) MySQL: http://sqlfiddle.com/#!9/a6f050/1 http://sqlfiddle.com/#!9/a6f050/1 Postgres: http://sqlfiddle.com/#!17/a8367/1 http://sqlfiddle.com/#!17/a8367/1
- adrianmonk 9y ago
  OK, so SQL extensions, so just the usual reasons to do bulk changes. I thought they might have mentioned it here because there was something unique to say about it in the context of this problem.
- imtringued 9y ago
  And preferably you use the postgres specific "unnest(array1, array2, ...)" function which turns array parameters passed into the query into a temporary table that you can query. UPDATE example SET position = CAST(temp.position AS INTEGER) FROM unnest(:ids, :positions) AS temp (id, position) WHERE example.id = CAST(temp.id AS INTEGER);
dllthomas 9y ago
idk if this is what the parent had in mind, but this seems straightforward enough: update t set user_order = case when id = 123 then 500 else user_order + 1 end where id = 123 or user_order >= 500;
etatoby 9y ago
This is just off the top of my head, but to move an item from position P to Q, you would do something like this: update items set pos = case when pos = P then Q when P < Q and pos between P and Q then pos - 1 when Q < P and pos between Q and P then pos + 1 else pos end Plus a where clause to limit the shuffling to the actual list being reordered, since you would keep all users' lists in the same table.

chris_wot 9y ago

If you do them in a transaction with the correct isolation level, it will appear as a single atomic update.