Updating Multiple Rows in MySQL

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?

The Scenario

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:

Id Value Created
1 1000001 2010-02-01
2 1000002 2010-02-01
3 1000003 2010-02-01
4 1000004 2010-02-01
5 1000005 2010-02-09
6 1000006 2010-02-26

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:

Id Value Created
1 100000109 2010-02-01
2 100000209 2010-02-01
3 100000309 2010-02-01
4 100000409 2010-02-01
5 1000005 2010-02-09
6 1000006 2010-02-26

As you can see, the column value whose date is 2010-02-01 was updated, in just a single SQL query.

This entry was posted in Database, MySQL, SQL and tagged , , , , , , , . Bookmark the permalink.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *