User-defined Order in SQL

Re:User-defined Order in SQL by Beanis on Wednesday March 21, 2018 @03:58PM (#16641759)

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

[ Parent | on HN ]

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

[ Parent | on HN ]

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.

[ Parent | on HN ]