SQLite – Insert with Select Syntax

sqlite

I'm trying to create a categorical column, with 3 states:
subA, subB or Neither.

I'm working with SqliteStudio 3.1.0, RHEL7.0.

I loaded the my csv into SQLITE via Python/Pandas. Everything works as expected.

I added constraints in the GUI:

"fullpath" TEXT Unique NOT NULL.

I added the Sub columns in SqiteStudio and double checked in Data table.

alter table data add column Sub text(100)

That works, no errors given. Then I try:

insert into  data(Sub)
select  fullpath, case 
        when fullpath like "%subA%" then "subA" 
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

I get this error which I don't understand.

[18:35:45] Error while executing SQL query on database 'test': 2 values for 1 columns

Best Answer

You are doing an insert into but you only specify one field (sub), then you select 2 fields, just as the error message says.

insert into  data(Sub) -- only one field
select  fullpath, case -- 2 fields
        when fullpath like "%subA%" then "subA" 
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

You would either need to add a fullpath field to the data table and do:

insert into  data(fullpath, Sub)
select  fullpath, case 
        when fullpath like "%subA%" then "subA" 
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

Or you need to select one column

insert into  data(Sub)
select  case 
        when fullpath like "%subA%" then "subA" 
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data
Related Question