What's the correct syntax for this Postgres query in MySQL?

Question

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

Answer 1

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 ;

Answer 2

In the SQL standard, there is no way to join two tables in an update statement.

An generic way to rewrite your statement would be:

UPDATE items
  SET position = case id
                   when 1 then 12
                   when 2 then 13
                   when 3 then 11
                 end
where id in (1,2,3);

This is however less flexible for modifications, but should run on any DBMS.

Answer 3

When there are joins involved, it's very hard to write an UPDATE that would work in many DBMS because each one has implemented this differently.

The proposed solution for MySQL wil not work for SQLite (as far as I know).

The syntax in the question that works in Postgres, will not work in either MySQL or SQLite.

I think this version will work in all three:

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

The solution suggested by @a_horse_with_no_name is similar but much simpler. I'd prefer that unless you have thousands of rows to update. In that case, you may want to use a temporary table:

CREATE TABLE i_vals
  ( id INT NOT NULL PRIMARY KEY,
    position INT NOT NULL
  ) ;

INSERT INTO i_vals
  (id, position)
VALUES
  (1, 12),
  (2, 13),
  (3, 11) ;

UPDATE items
SET position = 
    ( SELECT i_vals.position
      FROM   i_vals
      WHERE  i_vals.id = items.id
    )
-- WHERE items.id IN (SELECT id FROM ivals) ;
WHERE EXISTS 
      ( SELECT 1 
        FROM ivals
        WHERE i_vals.id = items.id
      ) ;

DROP TABLE i_vals ;