Update one column data to another column in MySQL if the second column is NOT NULL?

To update one column data to another column, you can use UPDATE command. 

Let us first create a table −

mysql> create table DemoTable
(
   UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   UserFirstName varchar(20),
   ListOfName varchar(20)
);
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable(UserFirstName,ListOfName) values('John','Larry');
Query OK, 1 row affected (0.29 sec)
mysql> insert into DemoTable(UserFirstName,ListOfName) values('Carol',null);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(UserFirstName,ListOfName) values('David','Sam');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable(UserFirstName,ListOfName) values('Bob',null);
Query OK, 1 row affected (0.25 sec)
mysql> insert into DemoTable(UserFirstName,ListOfName) values(null,null);
Query OK, 1 row affected (0.18 sec)

Following is the query to display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+--------+---------------+------------+
| UserId | UserFirstName | ListOfName |
+--------+---------------+------------+
| 1      | John          | Larry      |
| 2      | Carol         | NULL       |
| 3      | David         | Sam        |
| 4      | Bob           | NULL       |
| 5      | NULL          | NULL       |
+--------+---------------+------------+
5 rows in set (0.00 sec)

Following is the query to update one column data with another column if the 2nd column is NOT NULL −

mysql> update DemoTable set UserFirstName=ListOfName where ListOfName is not null;
Query OK, 2 rows affected (0.26 sec)
Rows matched: 2 Changed: 2 Warnings: 0

Let us check the column UserFirstName is updated or not.

mysql> select *from DemoTable;

This will produce the following output −

+--------+---------------+------------+
| UserId | UserFirstName | ListOfName |
+--------+---------------+------------+
| 1      | Larry         | Larry      |
| 2      | Carol         | NULL       |
| 3      | Sam           | Sam        |
| 4      | Bob           | NULL       |
| 5      | NULL          | NULL       |
+--------+---------------+------------+
5 rows in set (0.00 sec)

Chandu yadav

Published on 29-Apr-2019 09:05:56

• Related Questions & Answers
• Update a column A if null, else update column B, else if both columns are not null do nothing with MySQL
• Insert default into not null column if value is null in MySQL?
• MySQL update column to NULL for blank values
• Update a column based on another MySQL table’s column
• Empty string in not-null column in MySQL?
• Removing NOT NULL restriction from column in MySQL?
• How to add a NOT NULL column in MySQL?
• Change a MySQL column to have NOT NULL constraint
• UPDATE column to append data into it in MySQL?
• SELECT not null column from two columns in MySQL?
• Set NOT NULL attribute to an existing column in MySQL
• How to sum cells in a column if a condition is met in another column with MySQL?
• Is it impossible to add a column in MySQL specifically before another column?
• Return value from a row if it is NOT NULL, else return the other row value in another column with MySQL
• Update column data without using temporary tables in MySQL?