SQLITE Insert with Select Syntax

Question

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

Answer 1

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