SQLite – Inserting Data with SELECT into New Column

sqlite

I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.

How things start:

SELECT COUNT(fullpath) FROM data;

12395

I use the column fullpath a lot so I create an index:

CREATE INDEX "index" ON data (fullpath);

I then add the column:

ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);

Insert values into the Sub column based on the fullpath:

insert into data(Sub)
select case
    when fullpath like '%subA%' then 'subA'
    when fullpath like '%subB%' then 'subB'
    else 'neither' end
from data;

Check the number of rows again:

SELECT COUNT(fullpath) FROM data;

24790

I don't understand the syntax and the operations. Do I need to join or something to respect the index?

Best Answer

You already solved your main problem!

But I want to suggest you to create a new table for sub and maybe also for fullpath, because you store the same values again and again in this row.

Something like this: 

CREATE TABLE path (id integer, name varchar(100));

Then populate it with a SELECT and add the ID to the main table!

Related Question