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)
Published on 29-Apr-2019 12:35:56
