SQL query to prepend prefix to existing value in a

问题:

I have searched and searched for an answer to this, and I think this must be child's play for anyone who KNOWS SQL (which is not me).

I want to insert a prefix to the values in a field of a table in my DB.

More specifically, I have a table jos_content in which I have a field 'title' (which contains the titles of the content items of my joomla site).

All the values in this field 'title' are names of individuals. Now all I want to do is add a prefix 'Mr.' to all the values of this field.

I can do this from phpmyadmin by clicking the edit pencil icon and simply adding Mr. in front of all the values but I have about 750 rows and an SQL command which can insert a prefix of 'Mr.' in front of all values of this field will be a great help.

I have read about the 'UPDATE' commands but that REPLACES the value with what you provide. But I want to let the values remain and add a prefix before them.

Please can anyone help me achieve this with a SQL command ?

Thanks a ton.

回答1:

You have no other conditions like update this in all rows then you can try

UPDATE jos_content SET title = CONCAT('Mr. ', title) 

if you want to update conditionally that means particular row needs to update the you can use

 UPDATE jos_content SET title = CONCAT('Mr. ', title)  where fiedl_name ='condition'

eg: UPDATE jos_content SET title = CONCAT('Mr. ', title)  where id = '1'

this will update only one row which contain id=1.

any way before doing this should keep a backup

回答2:

update tablename set title = 'Mr. ' || title where ....

回答3:

UPDATE jos_content SET title = CONCAT('Mr. ', title) WHERE 1

Before testing the query do make a database backup.

回答4:

just do it like this

If, for example, I want to add +symbol before the countrycode:

UPDATE [masters].[country] SET Countrycode = '+' +Countrycode