13

I need to update newly created column in my oracle table. To do so I need to use existing values in row to decide how to populate this column, I am getting error:

java.lang.NullPointerException -> See Debug Output for details

This is my query:

UPDATE
    SCHEMA_NAME.TABLE_NAME
SET
    OCO= IF CO= 'Y' AND COM='Y' THEN 
{
    'Y'
} ELSE
{
    'N'
}
END IF;

Any suggestions on syntax?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Jakub Zak
  • 1,212
  • 6
  • 32
  • 53

1 Answers1

23

You could use CASE expression in the SET clause.

For example,

UPDATE table
SET schema.column =  CASE
                        WHEN CO= 'Y' AND COM='Y' THEN
                          'Y'
                        ELSE
                          'N'
                     END
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • if the value of 'CO' or 'COM' was being updated in the same statement, will the case block consider old value of the 'CO' and 'COM' columns or the updated values? – gkapagunta Nov 29 '16 at 09:45
  • @gkapagunta Oracle follows the ACID property for transaction. Read more about it and you will find your answer in your question itself. By the way answer is NO. – Lalit Kumar B Nov 29 '16 at 17:34