SQL insert choice of column name dependant on select value

Hope the title is a reasonable description.

I have 2 tables in a db (sqlite).

CREATE TABLE files (
  name TEXT,
  sha256 TEXT,
  fsno INT
);

CREATE TABLE sha256 (
  sha256 TEXT,
  fs1 INT,
  fs2 INT,
  fs3 INT,
  fs4 INT
);

files.fsno ranges from 2 to 5.

I am doing:

INSERT OR IGNORE INTO sha256sums (sha256)  select sha256 from files;

Then:

update sha256sums set fs2 = 1 where sha256 in (select sha256 from files where fsno = 2);
update sha256sums set fs3 = 1 where sha256 in (select sha256 from files where fsno = 3);
update sha256sums set fs4 = 1 where sha256 in (select sha256 from files where fsno = 4);
update sha256sums set fs5 = 1 where sha256 in (select sha256 from files where fsno = 5);

Is there a way to replace these 4 updates with something in the INSERT OR IGNORE? Something like:

INSERT OR IGNORE INTO sha256sums (sha256, fs2, fs3, fs4, fs5)  
select 
sha256, fsno==2?1:0, fsno==3?1:0, fsno==4?1:0, fsno==5?1:0 
from files;

The DB is Sqlite.

TIA, Pete

Answer

You can use a case expression:

INSERT OR IGNORE INTO sha256sums (sha256, fs2, fn3, fs4, fsno5) 
    select sha256,
           (case when fsno = 2 then 1 end),
           (case when fsno = 3 then 1 end),
           (case when fsno = 4 then 1 end),
           (case when fsno = 5 then 1 end)
    from files;

This assumes that that sha256 are not duplicated in the table — that is, that you only want the new records to change.

Advertisement

Answer