Copy values of one column to another using INSERT and SELECT in a single MySQL query

Let us first create a table −

mysql> create table DemoTable1
(
   Name varchar(100),
   Gender ENUM('MALE','FEMALE')
);
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values('Chris','Male');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1 values('Emma','Female');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+-------+--------+
| Name  | Gender |
+-------+--------+
| Chris | MALE   |
| Emma  | FEMALE |
+-------+--------+
2 rows in set (0.00 sec)

Following is the query to create the second table −

mysql> create table DemoTable2
(
   EmployeeGender varchar(100)
);
Query OK, 0 rows affected (0.47 sec)

Let us now copy values of one column to another with a single MySQL query −

mysql> insert into DemoTable2(EmployeeGender) select Gender from DemoTable1;
Query OK, 2 rows affected (0.15 sec)
Records: 2 Duplicates: 0 Warnings: 0

Display all records from the table using select statement  −

mysql> select *from DemoTable;

This will produce the following output −

+----------------+
| EmployeeGender |
+----------------+
| MALE           |
| FEMALE         |
+----------------+
2 rows in set (0.00 sec)

AmitDiwan

Published on 24-Sep-2019 13:57:10

• Related Questions & Answers
• MySQL select and insert in two tables with a single query
• Copy column values from one table into another matching IDs in MySQL
• MySQL statement to copy data from one table and insert into another table
• How do I insert multiple values in a column with a single MySQL query?
• How to SELECT fields from one table and INSERT to another in MySQL?
• MySQL query for INSERT INTO using values from another table?
• MySQL query to select one specific row and another random row?
• MySQL query to increment one of the column values
• Easiest way to copy values of one column to a new table in MySQL?
• Insert values from the first table to the second table using two SELECT statements in a single MySQL query
• A single MySQL query to select value from first table and insert in the second?
• Copy from one column to another (different tables same database) in MySQL?
• Insert values in a table by MySQL SELECT from another table in MySQL?
• MySQL query to sort column values and ignoring quotes on one of the values
• Fastest way to insert with multiple values in a single MySQL query?