Syntax error UPDATE column to append data into it in MySQL?

UPDATE column to append data into it in MySQL?

To achieve this, the following is the syntax.

UPDATE yourTableName set
yourColumnName=concat(ifnull(yourColumnName,””),’anyValue1,anyValue2,anyValue);

To understand the above syntax, let us first create a table. The query to create a table is as follows -

mysql> create table AppendDataDemo
-> (
-> StudentId int,
-> StudentName varchar(100),
-> StudentAge int
-> );
Query OK, 0 rows affected (1.54 sec)

Insert some records in the table using insert command. The query is as follows.

mysql> insert into AppendDataDemo values(101,'John',23);
Query OK, 1 row affected (0.24 sec)

mysql> insert into AppendDataDemo values(102,null,24);
Query OK, 1 row affected (0.74 sec)

mysql> insert into AppendDataDemo values(103,'Mike',26);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. The query is as follows.

mysql> select *from AppendDataDemo;

The following is the output.

+-----------+-------------+------------+
| StudentId | StudentName | StudentAge |
+-----------+-------------+------------+
| 101       | John        | 23         |
| 102       | NULL        | 24         |
| 103       | Mike        | 26         |
+-----------+-------------+------------+
3 rows in set (0.00 sec)

The following is the query to update the column StudentName and append “Carol, Sam, Maria” to its data.

mysql> update AppendDataDemo set StudentName=concat(ifnull(StudentName,""), ' Carol,Sam,Maria');
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Check the table records from the table using select statement. The query is as follows.

mysql> select *from AppendDataDemo;

The following is the output displaying appened data.

+-----------+----------------------+------------+
| StudentId | StudentName          | StudentAge |
+-----------+----------------------+------------+
| 101       | John Carol,Sam,Maria | 23         |
| 102       | Carol,Sam,Maria      | 24         |
| 103       | Mike Carol,Sam,Maria | 26         |
+-----------+----------------------+------------+
3 rows in set (0.03 sec)
Updated on: 2020-06-29T11:15:14+05:30

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements