sqlite expression with conditional action

Question

I am attempting to move some code from c# to a sql statement. The purpose is speed and is warranted. The objective is to get the id of a select if it is found and do an update to it.
If it is not found, then try another select query and if that is found update that record. If THAT is not found then insert a record and use the id of that inserted record in an update to that id record

if  
   select id from table where field1 = "testcase" has a result then update the record andreturn
 else
   select id from table where field2 = "othercase" if there is a result then update the record and return
 else --no record found
 update table field3 = "xx" where id = insert into recorddata values

Read up on the dialect of sql understood by sqlite at https://www.sqlite.org/lang.html — Shawn, Oct 09 '19 at 22:36
Possible duplicate of [How do I perform an IF...THEN in an SQL SELECT?](https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select) — Richard Plester, Oct 10 '19 at 00:18

MikeT · Answer 1 · 2019-10-10T00:14:03.277

There is no if then else construct in SQLite, the equivalent is CASE WHEN THEN ELSE END. However, you can only undertake one statement as such at a time, although such statements can be quite complex.

I think that the following is along the lines of what you want but it is unclear what update the record means nor what insert a record means.

DROP TABLE IF EXISTS thetable;
CREATE TABLE IF NOT EXISTS thetable (id INTEGER PRIMARY KEY, field1, field2, field3);
INSERT INTO thetable (field1,field2,field3) VALUES('testcase','othercase','aa'),('nottestcase','notothercase','bb'),('nottestcase','othercase','cc'),('testcase','notothercase','dd');
SELECT * FROM thetable;
INSERT INTO theTable SELECT null,field1,field2,null FROM thetable WHERE NOT (field1 = 'testcase' OR field2 = 'othercase');
UPDATE theTable SET field3 = (SELECT id FROM thetable WHERE field3 IS NULL) WHERE (NOT (field1 = 'testcase' OR field2 = 'othercase')) AND field3 IS NOT NULL;
UPDATE theTable SET field3 = 'mynewfield3value' WHERE field1='testcase' OR field2 = 'othercase';
SELECT * FROM thetable;
DROP TABLE IF EXISTS thetable; /* cleanup test environment*/

The first 3 statements create the environment i.e. a populated table.
The 4th statement shows the table before any changes.
The 5th statement inserts a new row IF there is a row (or rows) for the --no record found condition.
- As the question is vague about what is inserted the values of field1 and field 2 or copied from the --no record row, the id (assuming that id is a typical id that is autogenereated) is autogenerated, field3 is NULL (this being used to distinguish the newly added row).
- If there are no --no record's then nothing will be inserted.
The 6th statement updates the other --found rows BUT NOT the newly inserted --wasn't even there row.
- This row could easily be changed is field3 being NULL is easily selected.
The 7th shows the table after the changes.
The 8th cleans up the environment (i.e. drops the table)

Result 1

The highlighted row is the --no record row.

Result 2

A new row with an id of 5 has been added.
The --no record row has been updated with the id of the new row.
Rows 1,3 and 4 (the --record found rows) have been updated.
It is unclear what should happen to the new row.

score 0 · Answer 2 · answered Oct 10 '19 at 00:09

Agree with comment: You need to do a little light reading before asking for your home work to be done ;) Read up on CASE in particular - clue in your own question "testcase","othercase".

Incidentally, SQL is a "declarative" language so you need to change your mindset from C# when programming with it - this will help going forward if you have a lot to do in SQL

Virtually exactly what you are looking for here.. https://www.sqlitetutorial.net/sqlite-case/

CASE case_expression
     WHEN when_expression_1 THEN result_1
     WHEN when_expression_2 THEN result_2
     ...
     [ ELSE result_else ] 
END

sqlite expression with conditional action

2 Answers2

Result 1

Result 2