I have a table called m0_curve and is in a SQLite database. In this table i have only 4 parameters in the table all real : ctrl,flow,power,pressure
when I execute this query :
SELECT CASE EXISTS (SELECT 1 FROM 'm0_curve' WHERE ctrl = '0' AND pressure = '117.21') WHEN 0 THEN ( UPDATE 'm0_curve' SET flow = '0' , power = '0' WHERE ctrl = '0' AND pressure = '117.21' ) ELSE INSERT INTO 'm0_curve' (ctrl,flow,power,pressure) VALUES( '0' , '0' , '0' , '117.21' ) END;
It gives me a syntax error : "Error: SQLITE_ERROR: near "UPDATE": syntax error" Looking through the sqlite manual I dont understand why?
Help would be appreciated
CodePudding user response:
For each new pressure in combination with a new ctrl I want to insert the value. If I already have that combination I want to update it
This means that you want the combination of ctrl
and pressure
to be unique and you can implement this with a unique index:
CREATE UNIQUE INDEX id_ctrl_pressure ON m0_curve(ctrl, pressure);
Then use UPSERT
to insert new rows:
INSERT INTO m0_curve (ctrl, flow, power, pressure) VALUES ('0', '0', '0', '117.21')
ON CONFLICT(ctrl, pressure) DO UPDATE
SET flow = EXCLUDED.flow,
power = EXCLUDED.power;
See the demo.