CODEWITHSUNDEEP
oracle-database
KS1
KS1

Reputation: 1039

Oracle update single table with multiple WHERE clauses

Just been doing some migration of data in our 11g and found a situation, where I would normally just create a few separate UPDATE scripts and got to thinking. Is there a way to do multiple updates with different WHERE clauses in the same script?

I mean instead of 

UPDATE table1 SET value1=X WHERE value1=A;
UPDATE table1 SET value1=Y WHERE value1=B;
UPDATE table1 SET value1=Z WHERE value1=C;

Could you do something nearer

UPDATE table1 SET value1=X WHERE value1=A,SET value1=Y WHERE value1=B, SET value1=Z WHERE value1=Z;

Obviously the above syntax is wrong, but that's my point. Can something like this be done, or do you just have to do the three separate scripts?

Thanks in advance

KS

Upvotes: 2

Views: 8118

Answers (3)

faustxp
faustxp

Reputation: 21

OR DECODE().

update table1 set
value1 = decode(value1, A, X
                      , B, Y
                      , C, Z
                         , value1
               )
where value1 in (A, B, C);

Upvotes: 2

gpeche
gpeche

Reputation: 22514

Use a CASE expression:

update table1
set value1 = case
        when value1 = A then X
        when value1 = B then Y
        when value1 = C then Z
    end
where
value1 in (A, B, C);

Upvotes: 2

Pankaj Sharma
Pankaj Sharma

Reputation: 1853

UPDATE table1 SET value1 = CASE
           WHEN value1=A THEN X
           WHEN value1=B THEN Y ELSE XYZ
        END;

Upvotes: 5

Related Questions