How to change value of

Question

I have a table that looks like this:

date|type|number
--------------------
28-jun-21,dog,23
28-jun-21,cat,24
28-jun-21,lion,23

I want to do a select statement that changes the dog value to hyena. So, it'd look like this:

date|type|number
--------------------
28-jun-21,hyena,23
28-jun-21,cat,24
28-jun-21,lion,23

Here is my code so far:

select date, case when type='dog' then type='hyena', number
from table1;

I am new to SQL and Oracle so any ideas or suggestions as to how to accomplish this would help.

Answer 1

All three of the identifiers you've chosen - "type", "date", and "number" - are reserved words in Oracle, and thus they must be quoted to be used as field names in a table. This also means they must be quoted every time they're used in an SQL statement, and so your SELECT statement should be something like:

SELECT "date",
       CASE "type"
         WHEN 'dog' THEN 'hyena'
         ELSE "type"
       END AS "type",
       "number"
  FROM TABLE1 

db<>fiddle here

Answer 2

That would be

select date,
       case when type = 'dog' then 'hyena'
            else type
       end as type,
       number
from table1

Though, note that date and number can't be used as column names, they are reserved for datatypes.