DEV Community

Abdurrahman Shofy Adianto
Abdurrahman Shofy Adianto

Posted on

1

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', ...)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more