- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Combine INSERT, VALUES, and SELECT in MySQL
You can combine the insert, values and select statement using below syntax
insert into yourFirstTableName(yourColumnName1,yourColumnName2,.......N) select yourColumnName1,yourColumnName2,.......N from yourSecondTableName where yourCondition;
To understand the above syntax, let us create two tables in which first table will get the record from the second table.
Let us create the first table without any records. The query to create a table is as follows
mysql> create table CombiningInsertValuesSelect -> ( -> EmployeeId varchar(10), -> EmployeeName varchar(100), -> EmployeeAge int -> ); Query OK, 0 rows affected (6.95 sec)
Now you can create the second table with some records. The query to create a table is as follows
mysql> create table getAllValues -> ( -> Id varchar(100), -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (1.12 sec)
Insert records in the second table with the name ‘getAllValues’ using insert command. The query is as follows
mysql> insert into getAllValues values('EMP-1','John',26); Query OK, 1 row affected (0.86 sec) mysql> insert into getAllValues values('EMP-2','Carol',22); Query OK, 1 row affected (0.36 sec) mysql> insert into getAllValues values('EMP-3','Sam',24); Query OK, 1 row affected (0.28 sec) mysql> insert into getAllValues values('EMP-4','David',27); Query OK, 1 row affected (0.25 sec) mysql> insert into getAllValues values('EMP-5','Bob',21); Query OK, 1 row affected (0.75 sec)
Now you can display all records from the table using select statement. The query is as follows
mysql> select *from getAllValues;
The following is the output
+-------+-------+------+ | Id | Name | Age | +-------+-------+------+ | EMP-1 | John | 26 | | EMP-2 | Carol | 22 | | EMP-3 | Sam | 24 | | EMP-4 | David | 27 | | EMP-5 | Bob | 21 | +-------+-------+------+ 5 rows in set (0.00 sec)
Here is the use of insert, values and select in MySQL. The query is as follows
mysql> insert into CombiningInsertValuesSelect(EmployeeId,EmployeeName,EmployeeAge) -> select Id,Name,Age from getAllValues where Id='EMP-4'; Query OK, 1 row affected (0.23 sec) Records: 1 Duplicates: 0 Warnings: 0
Now check the record is present in the table or not using select statement. The query is as follows
mysql> select *from CombiningInsertValuesSelect;
The following is the output
+------------+--------------+-------------+ | EmployeeId | EmployeeName | EmployeeAge | +------------+--------------+-------------+ | EMP-4 | David | 27 | +------------+--------------+-------------+ 1 row in set (0.00 sec)
- Related Articles
- Select and insert values with preceding zeros in a MySQL table
- Insert values in a table by MySQL SELECT from another table in MySQL?
- Combine SELECT & SHOW command results in MySQL?
- Insert record using MySQL SELECT?
- Copy values of one column to another using INSERT and SELECT in a single MySQL query
- Insert with a Select query in MySQL
- MySQL select and insert in two tables with a single query
- Perform multiple inserts with INSERT INTO SELECT and UNION in MySQL
- Best way to combine multiple advanced MySQL select queries?
- MySQL Select Multiple VALUES?
- Can we implement nested insert with select in MySQL?
- How to SELECT fields from one table and INSERT to another in MySQL?
- Insert row with only default values in MySQL
- MySQL Select where values IN List string?
- MySQL randomly select 2 values from column values?
