sqlite3 unable to select column from table in attached database

user_13970714 0
Viewed 93

I'm merging two similar databases (A and B) where I take entries in A that aren't in B and add them to B. I was trying to do it with an attached database (I used .open A.db and attach 'B.db' as B). Both have table C which contains column D. I need to do some inner joins on other tables, but first I need to do something like:

select D from C where not exists (select D from B.C where C.D = B.C.D);

And I get the error "no such column: B.C.D

Are you not allowed two dots in one expression? Do I need to somehow use "as" to fix that, or would that even work?

sqlite
2 Answers

If the 2 tables contain a column D then you should not get that error.

But, there is another problem in the subquery:

select D from B.C where C.D = B.C.D

C.D is interpreted as the column D of the table B.C and not the table A.C as you might expect.

Set an alias for the table A.C and use it inside the subquery:

select D from C AS x where not exists (select D from B.C where x.D = B.C.D);

Or, use EXCEPT:

select D from C 
EXCEPT
select D from B.C
user_104988280

Use quotes around those items that have dots in them

select D from C where not exists (select D from "B.C" where C.D = "B.C.D");

There is one question - where is the table "B" in your clause?

user_147516910
Related