Mysql – What’s the correct syntax for this Postgres query in MySQL

MySQLpostgresqlsqliteupdate

I have a query which works well in our PostgreSQL db, but we now need to run our app on a MySQL DB as well.

The query takes an array of IDs and positions, and updates each record with given ID with a new value for the respective position.

  UPDATE items
    SET position = i_vals.position 
    FROM (VALUES (1,12), (2, 13), (3,11)) AS i_vals(id, position)
    WHERE i_vals.id = items.id;

How can I rewrite this query to be MySQL and SQLite3 friendly? As a bonus, I'd like to be able to also run in PostgreSQL with the same query, but appreciate that might not be possible

Best Answer

It can be

UPDATE items, ( SELECT 1 id, 12 position UNION ALL 
                SELECT 2   , 13          UNION ALL
                SELECT 3   , 11                    ) i_vals
SET items.position = i_vals.position 
WHERE i_vals.id = items.id;

or:

UPDATE items
       JOIN   ( SELECT 1 id, 12 position UNION ALL 
                SELECT 2   , 13          UNION ALL
                SELECT 3   , 11                    ) i_vals
       ON  i_vals.id = items.id;
SET items.position = i_vals.position ;
Related Question