Need to find an optimum method to update multiple rows in ORACLE 9 using one SQL

Question

Issue desc: Need to match and update new desc(if different) production table from a flat file. Have imported flat file in temptable.

sample from prod table:
[mstr_plan]

char(5)    varchar2(30) char(3)
PLAN_CODE PLAN_DESC FIN_CLUSTER
BB123     Plan_desc3 Z01
BB124     Plan_desc4 Z02
BB125     Plan_desc5 Z02
BB126     Plan_desc6 Z03
BB127     Plan_desc7 Z04
BB128     Plan_desc8 Z06
<about 500 records>

tmptbl01 <new records from flat file)
PLAN_CODE PLAN_DESC    FIN_CLUSTER
BB123     Plan_desc3    Z01
BB124     Plan_desc4    Z02
BB125     Plan_desc51   Z02
BB126     Plan_desc61   Z03
BB127     Plan_desc7    Z04
BB128     Plan_desc81   Z06
<about 150 records>

Select query :

select * from mstr_plan, tmptbl01
where mstr_plan.plan_code = tmptbl01.plan_code and 
(mstr_plan.PLAN_DESC <> tmptbl01.PLAN_DESC or
mstr_plan.FIN_CLUSTER <> tmptbl01.FIN_CLUSTER);

<in my database with 500 & 150 rows it returns 17 rows>
<in sample should return 3 rows>
PLAN_CODE PLAN_DESC    FIN_CLUSTER
BB125     Plan_desc51   Z02
BB126     Plan_desc61   Z03
BB128     Plan_desc81   Z06

There are no changes in FIN_CLUSTER. So, took it out from update query.

UPDATE mstr_plan 
SET mstr_plan.PLAN_DESC = 
(select tmptbl01.plan_desc from mstr_plan, tmptbl01 where mstr_plan.plan_code=
 tmptbl01.plan_plan_code and mstr_plan.plan_desc <> mstr_plan.plan_desc )

where mstr_plan.PLAN_DESC = <or IN>
(select tmptbl01.plan_desc from mstr_plan, tmptbl01 where mstr_plan.plan_code=
 tmptbl01.plan_plan_code and mstr_plan.plan_desc <> mstr_plan.plan_desc )

returns ORA-01427: single-row subquery returns more than one row

Methods: <1> Manually update 17 records one by one <2> Read about a crude method to 'and where with rownum = 1'. Didn't try yet.

Please recommend me better method.

Answer 1

The error is because you have placed mstr_plan table inside the subqueries.

Even if you corrected that though, none of your UPDATE statements would work because they include and mstr_plan.plan_desc <> mstr_plan.plan_desc in the WHERE clause, making it always false.

It's usually better to use a variation of the SELECT you already have and correctly finds the rows to be updated. You only need to select only the column(s) to be updated and the new values and then update this derived table:

UPDATE 
    ( SELECT mstr_plan.plan_desc 
           , tmptbl01.plan_desc AS new_plan_desc 
      FROM mstr_plan 
        JOIN tmptbl01 
          ON mstr_plan.plan_code = tmptbl01.plan_code 
      WHERE mstr_plan.plan_desc <> tmptbl01.plan_desc  
    ) 
SET plan_desc = new_plan_desc ;

This way you have less chances of updating wrong rows in case you make a mistake in the code (if your WHERE was always true instead of always false you might have updated all the rows in your mstr_plan table, possibly placing some NULL values in the (500-17) rows that shouldn't be updated.

You can also use aliases to make it more readable:

UPDATE 
    ( SELECT m.plan_desc 
           , t.plan_desc AS new_plan_desc 
      FROM mstr_plan  m
        JOIN tmptbl01  t
          ON m.plan_code = t.plan_code 
      WHERE m.plan_desc <> t.plan_desc  
    ) 
SET plan_desc = new_plan_desc ;