Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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?
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.
958 Views
