Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
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)
92 Views
