Insert values into a table from multiple tables using sqlite query

Question

If I have Table1 as

A           B         C
1           b.1       c.1
2           b.2       c.2
1           b.3       c.3

My second table Table2 as

A          D         E        F         G
1          d.1       e.1      f.1       g.1
2          d.2       e.2      f.2       g.2

I need to insert into an empty Table3 the values from above such that it looks like this.

A            B          C            D            E
1            b.1        c.1          d.1          e.1
2            b.2        c.2          d.2          e.2
1            b.3        c.3          d.1          e.1

So basically I need to insert each row of Table1 into Table3. For each row I need to check for column A and find the corresponding value D and E from the column and insert into Table3. Is it possible to do this in one single query?

To copy Table1 to Table3 I can use the query

INSERT INTO Table3(A,B,C) SELECT A,B,C FROM Table1

And then I need to take each row from Table3 and using A update the values of D and E from Table2. Is there a better solution that I can use to insert directly from both tables to Table3? Any help is appreciated, as I am a beginner with database and queries.

CL. · Accepted Answer · 2015-09-10 11:06:45Z

3

To merge two tables, use a join:

-- INSERT ...
SELECT A, B, C, D, E
FROM Table1
JOIN Table2 USING (A);

This will not generate a result row if no matching Table2 row is found. If you want a result row in this case (with NULLs for the missing values), use an outer join instead.

answered Sep 10, 2015 at 11:06

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

gfernandes Over a year ago

Thank you for your reply. I think I should have added another condition. The Table2 has other columns F G H which I do not need in Table 3. Will JOIN still work?

CL. Over a year ago

Comments are not intended to ask new questions; use the "Ask Question" button for that. But you might just try it out instead …

Ashish Vora · Accepted Answer · 2015-09-10 12:36:41Z

0

INSERT INTO Table3 (A,B,C,D,E) 
SELECT t1.A, t1.B, t1.C, t2.D, t2.E FROM Table1 t1
INNER JOIN Table2 t2 ON t2.A = t1.A

This might solve your problem.

answered Sep 10, 2015 at 12:36

Ashish Vora

5711 gold badge8 silver badges27 bronze badges

Collectives™ on Stack Overflow

Insert values into a table from multiple tables using sqlite query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related