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.
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 ;