3 ms·

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 gi

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.

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.