Join two columns of sqlite3 table

Question

hi have two tables, one table contains a column named

Table A _id, Eng, Hindi

another table contains

Table B _id, Eng, Hindi

I want to join the two tables by matching the word with Eng , if the word Eng does not match, it will be added to table A, which is in table B, otherwise the table. Value will remain

eg

Now table B

Table a

The result will be in TABLE A

Answer 1

FULL JOIN is a term for joining rows from multiple tables. Not relevant to your needs.

You just need to insert in table a all the entries in table b that are not listed in table a .

 INSERT INTO TABLE a(Eng, Hindi) SELECT Eng, Hindi FROM b WHERE eng NOT IN (SELECT eng FROM a); 

Answer 2

If you just want to get ( SELECT ), the desired output and TableA may contain records that are not in TableB , then you can emulate FULL JOIN to achieve your goal

 SELECT e.eng, COALESCE(a.hindi, b.hindi) hindi FROM ( SELECT eng FROM TableB UNION SELECT eng FROM TableA ) e LEFT JOIN TableB b ON e.eng = b.eng LEFT JOIN TableA a ON e.eng = a.eng 

If, on the other hand, TableA always contains only a subset of eng values ​​of TableB , then you can simply use LEFT JOIN

 SELECT b.eng, COALESCE(a.hindi, b.hindi) hindi FROM TableB b LEFT JOIN TableA a ON b.eng = a.eng 

Here is the SQLFiddle demo

---

Now, if you want to update the contents of TableA and assuming A_id is AUTOINCREMENT , you can do

 INSERT INTO TableA (eng, hindi) SELECT b.eng, b.hindi FROM TableB b LEFT JOIN TableA a ON b.eng = a.eng WHERE a.eng IS NULL 

Here is the SQLFiddle demo

or

 INSERT INTO TableA (eng, hindi) SELECT b.eng, b.hindi FROM TableB b WHERE NOT EXISTS ( SELECT * FROM TableA WHERE eng = b.eng ); 

Here is the SQLFiddle demo