Hacker News Reader

Comment by etatoby | Hacker News Reader

Yes, ideally in a single UPDATE statement.

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.

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

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.

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

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;

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.

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

Reply on news.ycombinator.com