Help need with Update statement -- Efficient way — oracle-tech

    Forum Stats

  • 3,714,812 Users
  • 2,242,631 Discussions
  • 7,845,073 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Help need with Update statement -- Efficient way

user13115886
user13115886 Member Posts: 116 Blue Ribbon
edited November 2020 in SQL & PL/SQL

Hi,

COl1 COl2

---- ----

A123 IPX

A123 IPY

A123 IPZ

A123 NULL

B123 IPY

B123 IPZ

B123 NULL

C123 IPZ

C123 NULL


if col2 has IPX, Would like to update all rows with IPX

if col2 has IPY, would like to update all rows with IPY -- A123 IPY is alrdy updated with IPX from the above condition

if col2 has IPZ, would like to update all rows with IPZ -- B123 IPZ is alrdy updated with IPY from the second condition


Best way to write the update statement by keeping all in one single statement, instead of writing three separate update statements.

Please advise.


Thanks

Answers

  • Mätes
    Mätes Member Posts: 43 Blue Ribbon

    Hi,

    That's my result from what I understood from your question:

    update t

      set col1 = case col2

             when 'IPX' then val1

             when 'IPY' then val2

             when 'IPZ' then val3

             else val4

           end;

     where col2 is not null;    

    Regards,

    martin

Sign In or Register to comment.