CODEWITHSUNDEEP
sqlsqlitesql-updatesql-delete
Farrukh Arshad
Farrukh Arshad

Reputation: 1461

Deleting or Updating SQL Record

I have a table with following two fields

jobid |  statusCode

Now here is what I want to do, I want to "delete the reocrd with given jobid if statusCode is less than some value, otherwise update this record with new statusCode"

My question is is there some single SQL operation to perform above mentioned job or do I have to use multiple SQL "if - else" sort of queries. My DB is SQlite3

Regards, Farrukh Arshad.

Upvotes: 2

Views: 147

Answers (2)

Hmxa Mughal
Hmxa Mughal

Reputation: 394

Something like this should help:

CASE WHEN status_code < some_value 
THEN
  DELETE FROM MyTable
  WHERE jobid = ?
ELSE
  UPDATE MyTable
  SET statusCode = ?
  WHERE jobid = ?
END

Upvotes: 0

CL.
CL.

Reputation: 180121

SQLite has a command UPDATE OR REPLACE that combines updating and inserting, but this is only for the common case where you want to avoid inserting a duplicate.

The DELETE command does not have the functionality you want.

SQLite does not have any control flow statements, so your best bet is to execute both DELETE/UPDATE statements:

DELETE FROM MyTable
WHERE jobid = ?
  AND statusCode < ?;

UPDATE MyTable
SET statusCode = ?
WHERE jobid = ?;        -- does nothing if record was deleted

Upvotes: 1

Related Questions