Using CASE statement to INSERT values into multiple columns in SQLite

Question

I'm trying to use INSERT statement to add values to a few columns in a new table depending on the value of a column in another table. I'm writing in SQLite. I was inspired by this question in sqlite update trigger with multiple if/Case Conditions which used CASE statement in one of the columns, but I would like to apply to all the columns that I'm adding.

The code I have wrote is similar to:

CREATE TABLE machine_info (
    MachineType TEXT,
    MaxPower INTEGER,
    Frequency REAL);

INSERT INTO machine_info (MachineType, MaxPower, Frequency)
    VALUES (
    CASE WHEN parts.Machine = "A" THEN "Diode", 200, 60
         WHEN parts.Machine = "B" THEN "AC", 500, 50
         WHEN parts.Machine = "C" THEN NULL, 500, NULL 
    );

And the error message says Result: near ",": syntax error. Did I wrap the CASE statement at the wrong place?

Answer 1

You may use an INSERT INTO ... SELECT, but you'll need CASE expressions for each of the 3 columns:

INSERT INTO machine_info (MachineType, MaxPower, Frequency)
SELECT
    CASE Machine WHEN 'A' THEN 'Diode' WHEN 'B' THEN 'AC' END,
    CASE Machine WHEN 'A' THEN 200 WHEN 'B' THEN 500 WHEN 'C' 500 END,
    CASE Machine WHEN 'A' THEN 60 WHEN 'B' THEN 50 END
FROM machine_info
WHERE Machine IN ('A', 'B', 'C');