- 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
Insert the data into Table C IF The data is not in Table B while Comparing to Table A in MySQL?
For this, use left join on table A and B. Let us create the first table −
mysql> create table demo20 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (1.87 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo20 values(100,'John'); Query OK, 1 row affected (0.07 sec) mysql> insert into demo20 values(101,'Bob'); Query OK, 1 row affected (0.24 sec) mysql> insert into demo20 values(102,'Mike'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo20 values(103,'Carol'); Query OK, 1 row affected (0.15 sec)
Display records from the table using select statement −
mysql− select *from demo20;
This will produce the following output −
+------+-------+ | id | name | +------+-------+ | 100 | John | | 101 | Bob | | 102 | Mike | | 103 | Carol | +------+-------+ 4 rows in set (0.00 sec)
Following is the query to create second table −
mysql> create table demo21 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (1.70 sec)
Insert some records into the table with the help of insert command −
mysql> insert into demo21 values(100,'Sam'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo21 values(101,'Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo21 values(133,'Bob'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo21 values(145,'David'); Query OK, 1 row affected (0.15 sec)
Display records from the table using select statement −
mysql> select *from demo21;
This will produce the following output −
+------+-------+ | id | name | +------+-------+ | 100 | Sam | | 101 | Adam | | 133 | Bob | | 145 | David | +------+-------+ 4 rows in set (0.00 sec)
Following is the query to create third table −
mysql> create table demo22 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (1.39 sec)
Now, let’s say, demo20 has table name A, demo21 has B and demo22 has C. The query is as follows to insert data into table C if data is not in table B comparing to A −
mysql> insert into demo22(id,name) −> select tbl1.id,tbl1.name from demo20 tbl1 −> left join demo21 tbl2 on tbl2.id=tbl1.id −> where tbl2.id is null; Query OK, 2 rows affected (0.21 sec) Records: 2 Duplicates: 0 Warnings: 0
Display records from the table using select statement −
mysql> select *from demo22;
This will produce the following output −
+------+-------+ | id | name | +------+-------+ | 102 | Mike | | 103 | Carol | +------+-------+ 2 rows in set (0.00 sec)
- Related Articles
- How can we insert data into a MySQL table?
- MySQL statement to copy data from one table and insert into another table
- INSERT INTO table if a table exists in MySQL else implement CREATE TABLE and create the table
- How to write MySQL procedure to insert data into a table?
- Select some data from a database table and insert into another table in the same database with MySQL
- Insert data in a table in MySQL stored procedure?
- Insert data from one table to another in MySQL?
- Which PHP function is used to insert data into an existing MySQL table?
- How to insert Binary data into a table using JDBC?
- Insert JSON into a MySQL table?
- Update data in one table from data in another table in MySQL?
- A single MySQL query to insert records (not all) in the second table from the first table
- Execute INSERT if table is empty in MySQL?
- How to write a procedure to insert data in the table in phpMyAdmin?
- Where is the MySQL table data stored in Windows?
