There comes a time when you need to update multiple rows with different values. You can update multiple rows with the same value, but when dealing with different values for different rows, it becomes a difficult task. Yes there are CASE statements, but what if you have an indefinite number of combinations?
What I’m trying to do is to append a suffix ’09’ for a certain column of all rows that met a certain criteria. For example I have these records:
I wanted to append ’09’ to the end of the value column whose date created is ‘2010-02-01’. I actually found the solution somewhere using different example. The example I’ve found was incrementing an integer value by one. In my problem, I need to append a year code for archiving purposes. So this is the code.
UPDATE table_name SET value = CONCAT(value, '09') WHERE date_created = '2010-02-01';
And the result would make my data looks like below:
As you can see, the column value whose date is 2010-02-01 was updated, in just a single SQL query.