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
Top comments (0)