45

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.

But those new buttons though..
  • 21,377
  • 10
  • 81
  • 108
Sean2015
  • 611
  • 1
  • 6
  • 7

4 Answers4

98

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

Jobin
  • 8,238
  • 1
  • 33
  • 52
  • UPDATE `installations` ins SET ins.`meter_msn` = CONCAT(ins.`meter_msn`,'U') WHERE ins.`meter_msn` IN ('20373121578800', '20373153162100', '20373150840900', '20373150580101', '20373150577700', '20373150577200', '20373311012300', '20373150574901') It's not working for me – Moeez Oct 10 '18 at 09:29
  • I want to add `U` in front of each number. the number is in string format – Moeez Oct 10 '18 at 09:30
  • @MrFaisal `CONCAT('U ', ins.meter_msn)` is right for add in the front. – Jobin Oct 10 '18 at 11:29
6

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

Before testing the query do make a database backup.

Valentin Despa
  • 40,712
  • 18
  • 80
  • 106
5
update tablename set title = 'Mr. ' || title where ....
sufleR
  • 2,865
  • 17
  • 31
1

just do it like this

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

UPDATE [masters].[country] SET Countrycode = '+' +Countrycode
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Thivan Mydeen
  • 1,461
  • 11
  • 7