CODEWITHSUNDEEP
sqlsqlite
Marko Stanojevic
Marko Stanojevic

Reputation: 468

IF ELSE workaround in Sqlite

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

Upvotes: 0

Views: 337

Answers (1)

forpas
forpas

Reputation: 164089

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.

Upvotes: 3

Related Questions