Click here to Skip to main content
15,880,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I had sql query:

IF EXISTS(SELECT 1 FROM Amount WHERE ID = 1 AND ClientId IS NULL) 
   BEGIN
        UPDATE Amount SET Price = 11 WHERE ID = 1 AND ClientId IS NULL
        PRINT 'Updated'
  END
ELSE 
    BEGIN
         INSERT INTO Amount (ID, Price, ClientId) VALUES(1, 11, NULL)
         PRINT 'Inserted'
    END


What I have tried:

I wanted to write test for this script, so I used sqlLite package for that:

_connection = new SqliteConnection("DataSource=InMemorySample;Mode=Memory;Cache=Shared");
_connection.Open();      
var updateCommand = _connection.CreateCommand();
updateCommand.CommandText = script;
updateCommand.ExecuteNonQuery();


However I get error: `SQLite Error 1: 'near "IF": syntax error'.` How should sql look so that i could be tested with sql? Or maybe it is possible to use `SqlConnection` instead `SqlLiteConnection` for in memory database?
Posted
Updated 12-Sep-22 3:37am

From the MySQL documentation[^] for IF says you cannot use an IF outside of a FUNCTION.
 
Share this answer
 
 
Share this answer
 
SQLite allows for "UPSERT" - which is what you are trying to do. See SQLite Query Language: upsert[^] You will need to ensure that you are enforcing uniqueness - e.g. on the ID
 
Share this answer
 
Comments
Member 12885549 12-Sep-22 14:04pm    
I need this to work on both sql server and sqllite :(
PIEBALDconsult 12-Sep-22 19:28pm    
You may need to write a different statement for each database system.
CHill60 13-Sep-22 3:10am    
I agree with @PIEBALDconsult. Further, if you want to test SQL Queries you should not be using SQLite - see Solution 2, SQLite does not use Transact-SQL. Getting SQL queries to work in SQLite for the sake of testing will compromise your SQL Build. You might be better using MySQL which at least uses the same syntax.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900