Can you Chain Multiple Do Statements for different Where Conditions in SQlite Upsert Statement-CodePudding

Hi I am new to sqlite and have an Upsert command I am trying to run in SQLite. This is a snippet of the command that is running in a C# function:

$@"INSERT INTO {table} (ToolType, TdxFolderPath, LastProcessedDateFolder, 
                LastProcessedTimeFolder, TdxFileName, LastTriedTimeStamp, Status, ErrorType, ErrorMessage, RetryCount)
                Values (@ToolType, @TdxFolderPath, @LastProcessedDateFolder, @LastProcessedTimeFolder, @TdxFileName,
                @LastTriedTimeStamp, @Status, @ErrorType, @ErrorMessage, @RetryCount)
                    ON CONFLICT (TdxFileName) 
                    DO UPDATE SET ErrorType=excluded.ErrorType, ErrorMessage=excluded.ErrorMessage, RetryCount = RetryCount   1
                        WHERE excluded.ErrorMessage = ErrorMessage
                    DO UPDATE SET ErrorType=excluded.ErrorType, ErrorMessage=excluded.ErrorMessage, RetryCount = 0
                        WHERE excluded.ErrorMessage != ErrorMessage";

The table variable is a table called 'ErrorLog_Items' with uniqueness constraints on id and TdxFileName. I want to do an update on several columns if the filename exists already. However if the filename exits already, the errormessage I am inserting might be the same as the existing error message in the table. In this situation I just want to update some of the columns and increment retrycount.

However if the new errormessage is different from the exisiting errormessage in table, I want to reset retrycount to 0 in addition to updating some of the columns as shown in the command above.

Is there a way to do this in one query? Or would it require multiple queries?

CodePudding user response：

You can do it with a CASE expression:

.....................................
ON CONFLICT(TdxFileName) 
DO UPDATE SET ErrorType = excluded.ErrorType, 
              ErrorMessage = excluded.ErrorMessage, 
              RetryCount = CASE WHEN excluded.ErrorMessage = ErrorMessage THEN RetryCount   1 ELSE 0 END

and there is no need for a WHERE clause.