2

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
5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.