Efficient way to write SQL

Question

I have a table that is populated by complex SQL in stored procedure. I'm thinking to write UPDATE statement on BILL_ADJ_MSG after it is populated, as I do not want to manipulate the existing complex logic.
The requirement is to suppress message codes '161' and '162' if any other message code exists. However, keep '161' and '162' message code of no other message code exists. suppressing message would mean replace '161' OR '162' with ' '

Here is the example of existing sample data in BILL_ADJ_MSG :

B_NO    MSG_1   MSG_2   MSG_3   MSG_4
----------------------------------------
100     UA1     16       15      162
200     161     UA161           
300     162
400     161     162

Desired Output after update in in BILL_ADJ_MSG:

B_NO    MSG_1       MSG_2       MSG_3       MSG_4
-------------------------------------------------------------------------
100     UA1         16              15      
200                 UA161                       
300     162     
400     161         162

Any idea how to accomplish in most optimized way? I'm looking to update more about 2 millioin records

So far, I'm trying to work with UPDATE sql with CASE statement

UPDATE BILL_ADJ_MSG
SET MSG_1 = 
CASE
WHEN MSG_1 IN ('161','162')
THEN ' '
ELSE MSG_1
END,
---SAME FOR MSG_2, MSG_3, MSG_4 --
WHERE 
TRIM(MSG_1)||TRIM(MSG_2)||TRIM(MSG_3)||TRIM(MSG_4) NOT IN ('161162','162161', '161','162');

Answer 1

You want something like:

UPDATE BILL_ADJ_MSG
SET   MSG_1 = CASE WHEN MSG_1 NOT IN ('161','162') THEN msg_1 ELSE ' ' END,
      MSG_2 = CASE WHEN MSG_2 NOT IN ('161','162') THEN msg_2 ELSE ' ' END,
      MSG_3 = CASE WHEN MSG_3 NOT IN ('161','162') THEN msg_3 ELSE ' ' END,
      MSG_4 = CASE WHEN MSG_4 NOT IN ('161','162') THEN msg_4 ELSE ' ' END
WHERE ( msg_1 IN ('161','162') AND (  msg_2 NOT IN ('161','162') 
                                   OR msg_3 NOT IN ('161','162') 
                                   OR msg_4 NOT IN ('161','162') ) )
OR    ( msg_2 IN ('161','162') AND (  msg_1 NOT IN ('161','162') 
                                   OR msg_3 NOT IN ('161','162') 
                                   OR msg_4 NOT IN ('161','162') ) )
OR    ( msg_3 IN ('161','162') AND (  msg_1 NOT IN ('161','162') 
                                   OR msg_2 NOT IN ('161','162') 
                                   OR msg_4 NOT IN ('161','162') ) )
OR    ( msg_4 IN ('161','162') AND (  msg_1 NOT IN ('161','162') 
                                   OR msg_2 NOT IN ('161','162') 
                                   OR msg_3 NOT IN ('161','162') ) );

You may need to add additional checks if your columns can be NULL