Postgres bulk update:
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
Postgres: http://sqlfiddle.com/#!17/a8367/1
Re:User-defined Order in SQL
by imtringued on Thursday March 22, 2018 @09:03AM
(#16648247)
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);
Re:User-defined Order in SQL
by adrianmonk on Wednesday March 21, 2018 @05:29PM
(#16642599)
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.
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
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;
I guess you can always swap pairs of rows with subtraction, like swapping row 5 and 7 would be:
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.