CODEWITHSUNDEEP
sqlite
user121
user121

Reputation: 931

how can I merge columns from 2 tables with different number of rows using SQLite?

I have 2 tables, 'tab1' and 'tab2':

tab1 is:

col1
25.0
30.0
31.0
25.0

tab2 is:

col1    col2
25.0    11.0
30.0    99.0
31.0    57.0

I want to get the following merged table result by matching the col1 values in tab1 with col1 in tab2 (thus filling in using col2 values from tab2):

col1    col2
25.0    11.0
30.0    99.0
31.0    57.0
25.0    11.0

I am using this sqlite code currently:

INSERT INTO `merged_table1` 
SELECT * FROM tab1 LEFT JOIN tab2
ON tab1.col1 = tab2.col1;

However, the result is not correct (giving an extra column):

25  25  11
30  30  99
31  31  57
25  25  11

Upvotes: 0

Views: 784

Answers (1)

CL.
CL.

Reputation: 180020

If the columns actually have the same name, you can do the join using the USING clause, which automatically removes the duplicate column:

INSERT ...
SELECT *
FROM      tab1
LEFT JOIN tab2 USING (col1);

Otherwise, just tell the database which columns you want:

INSERT ...
SELECT tab1.col1,
       tab2.col2
FROM      tab1
LEFT JOIN tab2 ON tab1.col1 = tab2.col1;

Upvotes: 1

Related Questions