search

harelba / q

q - Run SQL directly on delimited files and multi-file sqlite databases
http://harelba.github.io/q/
GNU General Public License v3.0
10.15k stars 419 forks source link

File exists, but getting query error: no such table: #198

Open ryandward opened 5 years ago

ryandward commented 5 years ago

The first query works:

`$ q -H -d"," "SELECT * FROM JGI_strains.csv WHERE Platform='Illumina'"                
1,Hernandeziana,sp.,ATA11-4-KO1,Illumina
2,Aetokthonos,hydrillicola,B3-Florida,Illumina
3,Aphanocapsa,lilacina,HA4352-LM1,Illumina
4,Aphanocapsa,sp.,GSE-SYN-MK-11-07L,Illumina
5,Aphanothece,saxicola,GSE-SYN-MK-01-06B,Illumina
6,Aphanothece,sp.,CMT-3BRIN-NPC111,Illumina
7,Brasilonema,angustatum,HA4187-MV1,Illumina
...
`

But if I try an update, it fails:

$ q -H -d"," "UPDATE 'JGI_strains.csv' SET Platform='ILLUMINA' WHERE [Platform='Illumina'];
query error: no such table: JGI_strains.csv
bitti commented 5 years ago

It would be news to me that q supports anything else then select. If that is your usecase you probably need more scripting or just import it into a sqlite3 table to leverage its full power.

spotiris commented 5 years ago

I have also come across this today, and couldn't find any documentation saying that q supports (or does not support) UPDATE. A workaround is to output your updated column to another name, then use either q or cut to remove your unwanted column.