Home > Blockchain >  need help on update syntax error in sqlite?
need help on update syntax error in sqlite?

Time:07-15

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.

  • Related