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

I've encountered 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

Postgres definitely have the best syntax among the three, 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

Conclusion

Indeed, if you have the data in CSV format, or you have direct access to the database, its easier to just import it using dedicated SQL toolbox. However as this is not always the case, I think this article should be valuable, at least for myself in the future. Hope it helps