IF ELSE workaround in Sqlite

Question

I am trying to do a simple upsert query in SQL. This query works in SQL Server

IF (EXISTS (SELECT * FROM TableName WHERE SomeId = @SomeId AND SomeOtherId = @SomeOtherId))
    BEGIN
        UPDATE TableName 
        SET "SomeColumn" = @SomeValue 
        WHERE SomeId = @SomeId AND SomeOtherId = @SomeOtherId;
    END
    ELSE
    BEGIN
        INSERT INTO TableName VALUES (@SomeId, @SomeOtherId, @SomeValue);
    END

However, our Unit tests run on Sqlite and in general we want to write vanilla SQL, so this IF ELSE won't work. I was trying to pull it off with CASE WHEN but I can't get it right. Can somebody post a working Sqlite query?

Thanks in advance, Marko

Possible duplicate... https://stackoverflow.com/questions/7746306/sqlite-if-exists-clause — SS_DBA, Oct 02 '19 at 20:07
That didn't quite helped me since I needed query to be executed depending on EXISTS not just value return, but thx — Marko Stanojevic, Oct 03 '19 at 08:52

forpas · Accepted Answer · 2019-10-03T09:23:56.970

If there is (or you can create) a unique constraint for the combination of the columns SomeId and SomeOtherId, then you might use UPSERT like this:

INSERT INTO TableName(SomeId, SomeOtherId, SomeColumn)
VALUES(@SomeId, @SomeOtherId, @SomeValue)
ON CONFLICT(SomeId, SomeOtherId) DO 
UPDATE SET SomeColumn = @SomeValue;

If not then use 2 statements:

UPDATE TableName 
SET SomeColumn = @SomeValue 
WHERE SomeId = @SomeId AND SomeOtherId = @SomeOtherId;

INSERT INTO TableName (SomeId, SomeOtherId, SomeColumn) 
SELECT @SomeId, @SomeOtherId, @SomeValue
WHERE NOT EXISTS (
  SELECT 1 FROM TableName
  WHERE SomeId = @SomeId AND SomeOtherId = @SomeOtherId
);

The UPDATE statement will succeed only if there is a row under the conditions you specify, otherwise it will do nothing.
The INSERT statement will succeed only if there is not a row under the conditions you specify, otherwise it will do nothing.

Yup, worked. The semicolon near end in second query is typo, but engine wouldn't let me remove it myself. — Marko Stanojevic, Oct 03 '19 at 08:50

IF ELSE workaround in Sqlite

1 Answers1