Menu
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.
569
CREATE OR REPLACE VIEW yourViewName AS SELECT *FROM yourTableName; To understand the above syntax, let us create a table. Insert some records in the table using insert command.
On the Home tab, in the View group, click View, and then click Design View. On the Design tab, in the Query Type group, click Append. The Append dialog box appears. Next, you specify whether to append records to a table in the current database, or to a table in a different database.
The SQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match. Note: The existing records in the target table are unaffected.
The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement. UPDATE table_name SET column1 = value1, column2 = value2,...
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
149
update tablename set title = 'Mr. ' || title where .... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
