Conditional update statement in Oracle

Question

I have a problem with building a conditional update statement in Oracle. For understandability, I'll simplify the problem and basically my update statement should look like this:

UPDATE SAMPLE_TAB1 t 
   SET t.sample_column1 =NVL(t.sample_column1, **SOME LOGIC**);

The ***SOME LOGIC*** part should look like this: (Please consider this is just a pseudo code)

IF ((SELECT sample_column2 FROM SAMPLE_TAB2 
       WHERE sample_column2= sample_value2
       AND sample_column3  = sample_value3 )='FALSE' THEN 
   t.sample_column1 =0;
ELSE

   t.sample_column1 =(SELECT sample_column1 FROM SAMPLE_TAB3 
                         WHERE sample_column4= sample_value4
                         AND sample_column5  = sample_value5 )

END IF;

Any kind of thoughts on this problem would be welcome. Thank you.

Jasti · Accepted Answer · 2013-08-30 17:29:26Z

12

Try the following code

UPDATE SAMPLE_TAB1 t 
    SET t.sample_column1 = (
      case when ((SELECT sample_column2 FROM SAMPLE_TAB2 
                          WHERE sample_column2= sample_value2 
                          AND sample_column3  = sample_value3 ) = 'FALSE' )
            then 0
      else
          (SELECT sample_column1 FROM SAMPLE_TAB3 
                         WHERE sample_column4= sample_value4
                         AND sample_column5  = sample_value5 )
      end

    )
  WHERE t.sample_column1 is not null;

answered Aug 30, 2013 at 17:29

Jasti

9475 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

schurik · Accepted Answer · 2013-08-30 16:13:17Z

1

try the following

UPDATE SAMPLE_TAB1 t 
   SET t.sample_column1 = NVL( (SELECT sample_column2  FROM ...), 0)
WHERE t.sample_column1 is not null
;

answered Aug 30, 2013 at 16:13

schurik

7,9582 gold badges26 silver badges30 bronze badges

1 Comment

Dimuthu Over a year ago

Hi what about the SAMPLE_TAB3 related logic then

Collectives™ on Stack Overflow

Conditional update statement in Oracle

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related