CODEWITHSUNDEEP
sqlite
Verena Praher
Verena Praher

Reputation: 1272

Why do I get the error "Error: no such column: main.<tablename>.<columname>" although it exists?

This works:

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /path/to/db
2    uni              /path/to/db

also this:

sqlite> pragma main.table_info(tsv_storage);
0|id|int|0||0
1|seqid|text|0||0
...

and this:

sqlite> select count(*) from main.tsv_storage;
198159

and also the attached database works:

sqlite> select * from uni.fasta_storage where uni.fasta_storage.id = 1;
1 MASNTVSAQ...  Q197F8.1 002R_IIV3 Uncharacterized protein 002R  Q197F8

but this not:

sqlite> select main.tsv_storage.seqid where main.tsv_storage.id=8;
Error: no such column: main.tsv_storage.seqid

EDIT: and I have also problems with this, do I have to join the tables?

insert into main.tsv_storage(seqlength) select length(fasta) from 
uni.fasta_storage where uni.fasta_storage.title = main.tsv_storage.seqid;
Error: no such column: main.tsv_storage.seqid

It happens for all columns, not only seqid. I think I did everything that is explained here: http://sqlite.awardspace.info/syntax/sqlitepg12.htm

What am I missing?

Upvotes: 0

Views: 731

Answers (2)

CL.
CL.

Reputation: 180040

When using INSERT ... SELECT ..., the SELECT part must be valid query.

You cannot access a column like main.tsv_storage without having the table in the FROM clause:

INSERT INTO main.tsv_storage(seqlength)
SELECT length(fasta)
FROM uni.fasta_storage, main.tsv_storage
WHERE uni.fasta_storage.title = main.tsv_storage.seqid;

And the entire commands looks suspicious. Are you sure you don't want to update the values in the seqlength column for existing records? In that case, you would use something like this:

UPDATE main.tsv_storage
SET seqlength = (SELECT length(fasta)
                 FROM uni.fasta_storage
                 WHERE uni.fasta_storage.title = main.tsv_storage.seqid);

Upvotes: 0

Scott
Scott

Reputation: 21882

sqlite> select * from main.tsv_storage.seqid where main.tsv_storage.id=8;

You have not defined where to look for the selection. You need to tell the query what fields to search within the table, then define which table you are searching. The select * portion tells the query to look in all fields within the table. The from portion of the query tells the processes what table to look in. And lastly the where portion tells the query what to match when looking.

Upvotes: 1

Related Questions