Site icon David Yin's Blog

How to add one string to all rows of one column

I have a MySQL database. There is one table has thousands rows of data. I need to add one string to every rows of one column.

It is a very popular scenario. Looks like in the middle of using one database, the developer need a new column with a default value in it.

Yes, he can setup a default value, but what about the existing data. The answer is shown below.

sample_table

+------+------+--------------+
| fieldA |  fieldB        |  fieldC | 
|  22    |  star          |     2   |
|  23    |  super         |     2   | 
|  24    |  savemore      |     1   |
|  25    |  justtolook    |     4   |
|  26    |  bestbook      |     4   |

I add this string to all rows of fieldB, just after the existing data.

The SQL script is used to do this job shown as below

update sample_table set fieldB  = concat(fieldB ,'.html');

Then after this script.

+------+------+--------------+
| fieldA | fieldB                 | fieldC |
| 22      | star.html            | 2 |
| 23      | super.html         | 2 |
| 24      | savemore.html  | 1 |
| 25      | justtolook.html   | 4 |
| 26      | bestbook.html    | 4 |
Exit mobile version