CODEWITHSUNDEEP
sqlsqlite
Devolus
Devolus

Reputation: 22084

Select with if condition in SQLite

I have some rows in my SQLite3 database where a status is stored. Normaly this status is a single word, But in some cases there is a reference number added to this status.

So for example the status can contain values like:

Created
Defined
Converted from xxx <- where xxx is the unique reference number
Closed / changed from xxx
...

and so on.

Now I want to map various states into different states because for my purpose some of these states have the same meaning (i.E. Created and Defined for example).

In Oracle I could use, at least for the unique strings, the decode function. However how can I process the values with the unique id. Is there something like a decode_like function that I can use for this?

The result should be:

 Created -> CRT
 Defined -> CRT
 Converted from xxx -> CVT
 Closed / changed from xxx -> CL

etc.

Can this even be done with an SQL or will I have to implement this conversion in my java code?

Upvotes: 0

Views: 87

Answers (1)

CL.
CL.

Reputation: 180060

Use the CASE expression:

UPDATE MyTable
SET Status = CASE
             WHEN Status = 'Created' OR Status = 'Defined' THEN 'CRT'
             WHEN Status LIKE 'Converted from %'           THEN 'CVT'
             WHEN Status LIKE 'Closed / changed from %'    THEN 'CL'
                                                           ELSE Status
             END

Upvotes: 1

Related Questions