Friday, May 31, 2013

MySQL Example of using CONCAT() and UPDATE

MySQL Example of using CONCAT() and UPDATE


This code updates the column named 'phone_number' in the table called 'user' by concatenating '0' in front of the new phone_number. 

The new phone_number is old phone_number minus the first 4 characters or beginning from the 5th character. 

The update will only be applied to the records with id between 3 and 30 exclusive.

UPDATE user SET phone_number = CONCAT('0', SUBSTRING(phone_number, 5)) WHERE id > 3 AND id < 30;

See how the code is shorter than the explanation?

Biniam from Ethiopia.

This code is posted at http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#c12657