Database:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
Problem:
I screwed up a field, by incorrectly updating a subset of records that should not be touched. We cannot easily restore the field, due to the backup procedure that we use.
As an alternative way to resolve the issue, I want to copy the values from the Routing Master Table and apply them to the Work Order Routing table.
So theoretically we can have 100 distinct masters that are used to up date 1000 records in the other table.
I cannot use the standard update to do this, and I could not successully use nested queries in an update statement. I am also having trouble using a merge.
This problem is not unique (I'm sure) so I need to know what I am doing wrong.
Please help.
Merge:
MERGE INTO wo_rtg
USING (
select wo_rtg.ccn, wo_rtg.mas_loc, wo_rtg.wo_num, wo.item, wo.revision, wo_rtg.wo_line, wo_rtg.operation, wo_rtg.oper_type, wo_rtg.op_fix_lt
from (( wo inner join wo_rtg
on ( wo.ccn = '1'
and wo.mas_loc = '1'
and wo.ccn = wo_rtg.ccn
and wo.mas_loc = wo_rtg.mas_loc
and wo.wo_num = wo_rtg.wo_num
and wo.wo_line = wo_rtg.wo_line
and (wo.status = 'R' or wo.status = 'I')
and wo_rtg.oper_type = 'O'
and wo_rtg.op_fix_lt = 5
--and wo.item = '114R2050-35'
--and wo.revision = 'AD'
)
) --1
inner join routing rt
on ( wo.item = rt.item
and wo.revision = rt.revision
and wo.ccn = rt.ccn
and rt.bcr_type = 'CUR'
and wo_rtg.operation = rt.operation
and trim(rt.bcr_alt) is null
)
) --2
) e
ON ( wo_rtg.ccn = e.ccn
and wo_rtg.mas_loc = e.mas_loc
and wo_rtg.wo_num = e.wo_num
and wo_rtg.wo_line = e.wo_line
and wo_rtg.operation = e.operation
and wo_rtg.oper_type = e.oper_type
)
WHEN MATCHED THEN
UPDATE SET r.op_fix_lt = e.op_fix_lt
WHEN NOT MATCHED THEN
insert into reason VALUES ('1','test', 'test test test','test','test','test',1,1,1,sysdate,1,sysdate,'id');
WHERE r.oper_type = 'O'
and r.op_fix_lt = 5
I tried this without the WHEN NOT MATCHED & INSERT clauses and the transaction does not work.
__________________________________________________________
Update Syntax:
update wo_rtg
set wo_rtg.op_fix_lt =
(
select rt.op_fix_lt
from (( wo inner join wo_rtg
on ( wo.ccn = '1'
and wo.mas_loc = '1'
and wo.ccn = wo_rtg.ccn
and wo.mas_loc = wo_rtg.mas_loc
and wo.wo_num = wo_rtg.wo_num
and wo.wo_line = wo_rtg.wo_line
and (wo.status = 'R' or wo.status = 'I')
and wo_rtg.oper_type = 'O'
and wo_rtg.op_fix_lt = 5
--and wo.item = '114R2050-35'
--and wo.revision = 'AD'
)
) --1
inner join routing rt
on ( wo.item = rt.item
and wo.revision = rt.revision
and wo.ccn = rt.ccn
and rt.bcr_type = 'CUR'
and wo_rtg.operation = rt.operation
and trim(rt.bcr_alt) is null
)
) --2
) -- nested select
Where (wo_rtg.ccn, wo_rtg.mas_loc, wo_rtg.wo_num, wo_rtg.wo_line, wo_rtg.operation, wo_rtg.oper_type)
in (
select wo_rtg.ccn, wo_rtg.mas_loc, wo_rtg.wo_num, wo.item, wo.revision, wo_rtg.wo_line, wo_rtg.operation, wo_rtg.oper_type, wo_rtg.op_fix_lt, rt.op_fix_lt as rtlt
from (( wo inner join wo_rtg
on ( wo.ccn = '1'
and wo.mas_loc = '1'
and wo.ccn = wo_rtg.ccn
and wo.mas_loc = wo_rtg.mas_loc
and wo.wo_num = wo_rtg.wo_num
and wo.wo_line = wo_rtg.wo_line
and (wo.status = 'R' or wo.status = 'I')
and wo_rtg.oper_type = 'O'
and wo_rtg.op_fix_lt = 5
--and wo.item = '114R2050-35'
--and wo.revision = 'AD'
)
) --1
inner join routing rt
on ( wo.item = rt.item
and wo.revision = rt.revision
and wo.ccn = rt.ccn
and rt.bcr_type = 'CUR'
and wo_rtg.operation = rt.operation
and trim(rt.bcr_alt) is null
)
) --2
)
{color:#ff0000}ORA-01427: single-row subquery returns more than one row
{color}{color:#000000}__________________________________________________
*Just to show you an idea of what I {color:#00ff00}+really +want to do .*{color}
update wo_rtg
set wo_rtg.op_fix_lt = rt.op_fix_lt
from (( wo inner join wo_rtg
on ( wo.ccn = '1'
and wo.mas_loc = '1'
and wo.ccn = wo_rtg.ccn
and wo.mas_loc = wo_rtg.mas_loc
and wo.wo_num = wo_rtg.wo_num
and wo.wo_line = wo_rtg.wo_line
and (wo.status = 'R' or wo.status = 'I')
and wo_rtg.oper_type = 'O'
)
) --1
inner join routing rt
on ( wo.item = rt.item
and wo.revision = rt.revision
and wo.ccn = rt.ccn
and rt.bcr_type = 'CUR'
and wo_rtg.operation = rt.operation
)
) --2
Where wo_rtg.ccn = wo.ccn
and wo_rtg.mas_loc = wo.mas_loc
and wo_rtg.wo_num = wo_rtg.wo_num
and wo_rtg.wo_line = wo_rtg.wo_line
and wo_rtg.operation = rt.operation
Please help!!{color}