Syntax error Update all varchar column rows to display values before slash in MySQL?

Update all varchar column rows to display values before slash in MySQL?

For this, use UPDATE command along with SUBSTRING_INDEX(). Let us first create a table −

mysql> create table demo69
−> (
−> name varchar(40)
−> );
Query OK, 0 rows affected (5.04 sec)

Insert some records into the table with the help of insert command −

mysql> insert into demo69 values('John/Smith');
Query OK, 1 row affected (0.83 sec)

mysql> insert into demo69 values('David/Miller');
Query OK, 1 row affected (0.23 sec)

mysql> insert into demo69 values('Chris/Brown');
Query OK, 1 row affected (0.40 sec)

mysql> insert into demo69 values('Carol/Taylor');
Query OK, 1 row affected (0.36 sec)

Display records from the table using select statement −

mysql> select *from demo69;

This will produce the following output −

+--------------+
| name         |
+--------------+
| John/Smith   |
| David/Miller |
| Chris/Brown  |
| Carol/Taylor |
+--------------+
4 rows in set (0.03 sec)

Following is the query to update all varchar column rows −

mysql> update demo69
−> set name=substring_index(name,'/',1);
Query OK, 4 rows affected (0.13 sec)
Rows matched: 4 Changed: 4 Warnings: 0

Display records from the table using select statement −

mysql> select *from demo69;

This will produce the following output −

+-------+
| name  |
+-------+
| John  |
| David |
| Chris |
| Carol |
+-------+
4 rows in set (0.00 sec)
Updated on: 2020-11-20T07:29:12+05:30

230 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements