[sqlite] Selecting data from two tables

Question

Hi there,

I have a SQL table which has an ID column and some other columns. The second table has also ID column. Now I want to insert into second table rows from first table, but only those that don't appear in it already. I want the second table to be "a sum" of both, but without duplicates.

The first one is labels and the second is tags. Both has label column. I want to insert labels.label, "", "", "", "N", "N/A" into tags for all labels found in labels table but not in tags, because labels in tags are already some data filled in.

I've tried

INSERT INTO tags SELECT labels.label, "", "", "", "N", "N/A" FROM labels, tags
WHERE labels.label != tags.label

but I'm getting a lot of duplicates. The same applies for

INSERT INTO tags SELECT labels.label, "", "", "", "N", "N/A" FROM labels
JOIN tags ON labels.label != tags.label

Maybe I just don't get an idea, because I'm quite new into SQL.

How can I achieve that? I'm using SQLite with Python.

Cheers, Tom

Answer 1

You could start here

insert into t2 (col1, col2 ...)
select colx, coly, ... from t1
where t1.ID not in
(select ID from t2)
)

The insert into syntax assumes that the second table (t2) already exists. If you wanted to create the new table, you'd select col1, col2 ... into NewTable.

EDIT: Changed "exists" to "in" b/c SQL Lite prefers "in".