How to update multiple row based on list of key-val pairs (in MySQL, MariaDB, & PostgreSQL)

howto.md

How to update multiple row based on list of key-val pairs (in MySQL, MariaDB, & PostgreSQL)

I've encoutered the needs for this technique multiple times. Mostly when the data is generated from other applications, and my only access to the database is via web-based SQL interface such as Adminer or PHPMyAdmin. It seems that currently there are no comprehensive article outlining how to achieve this. So I'll just write my findings here.

Mysql 5.x

as this version doesn't support VALUES clause yet, our only choice is to use the ugly CASE-WHEN syntax:

UPDATE table_name
SET changed_col = CASE comparison_col
           WHEN 'key1' THEN 'value1'
           WHEN 'key2' THEN 'value2'
           ...
        END
WHERE comparison_col in ('key1', 'key2', ...)

reference: https://stackoverflow.com/a/13067614

Mysql 8.x

Luckily this version introduced VALUES clauses, so we could write the data more concise using VALUES ROW(..), ROW(..) syntaxes

UPDATE table_name
SET changed_col = temp_data.column1
FROM (VALUES
  ROW('key1', 'val1'),
  ROW('key2', 'val2'),
  ....
) as temp_data
WHERE comparison_col = temp_data.column0

https://dev.mysql.com/doc/refman/8.0/en/values.html

MariaDB 10.x

MariaDB's VALUES clause is shorter as it doesn't use the ROW keyword at all

UPDATE table_name
SET changed_col = temp_data.column1
FROM (VALUES
  ('key1', 'val1'),
  ('key2', 'val2'),
  ....
) as temp_data
WHERE comparison_col = temp_data.column0

PostgreSQL, SQL Server

Postgres definitely have the best syntax as it support aliasing column names for VALUES syntax, just as SQL Server did

UPDATE table_name
SET changed_col = temp_data.col_name_1
FROM (VALUES 
        ('key1', 'val1'),
        ('key1', 'val2'),
        ...
) temp_data (col_name_1, col_name_2)
WHERE comparison_col = temp_data.col_name_2

reference: https://stackoverflow.com/a/18799497