- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Update a column value, replacing part of a string in MySQL?
152 Lectures 16 hours
87 Lectures 5.5 hours
To update a column value, the update command as well as the replace method can be used. The steps to better understand these are given as follows −
First create a table with the help of the create command. This is given as follows −
mysql> CREATE table DemoOnReplace -> ( -> Id int, -> Name varchar(200) -> ); Query OK, 0 rows affected (0.63 sec)
After successfully creating a table, some records are inserted with the help of the insert command. This is shown below −
mysql> INSERT into DemoOnReplace values(1,'John'); Query OK, 1 row affected (0.10 sec) mysql> INSERT into DemoOnReplace values(1,'David warner'); Query OK, 1 row affected (0.15 sec)
Now, all the table records can be displayed with the help of the select command which is given as follows −
mysql> SELECT * from DemoOnReplace;
After executing the above query, the following output is obtained −
+------+--------------+ | Id | Name | +------+--------------+ | 1 | John | | 1 | David warner | +------+--------------+ 2 rows in set (0.00 sec)
The syntax to update a column value is as follows −
UPDATE yourTableName set column_name = REPLACE(column_name , 'oldvalue', 'NewValue');
The above syntax is used to replace part of the string with update command. This is given as follows −
mysql> UPDATE DemoOnReplace set name = REPLACE(name, 'David warner', 'David warner S.'); Query OK, 1 row affected (0.18 sec) Rows matched: 2 Changed: 1 Warnings: 0
The output of the above query is as follows −
+------+-----------------+ | Id | Name | +------+-----------------+ | 1 | John | | 1 | David warner S. | +------+-----------------+ 2 rows in set (0.00 sec)
As can be seen from the above output, a part of the string is replaced by updating a column value.
- Sort a MySQL table column value by part of its value?
- Update only a single column value in MySQL
- Replace part of string in MySQL table column?
- Get part of a string based on a character in MySQL?
- Split the left part of a string by a separator string in MySQL?
- Update a column of text with MySQL REPLACE()
- How to cast and update a numeric value from string column only where applicable in MySQL?
- How to cut part of a string with a MySQL query?
- How to update a MySQL column by subtracting a value with some conditions?
- How to prepend a string to a column value in MySQL?
- Replacing Substrings in a Java String
- How to update MySQL column with random value?
- Update a MySQL column with JSON format?
- How to order by certain part of a string in MySQL?
- Fetch middle part of a string surrounded by slash in MySQL