Link to home
Start Free TrialLog in
Avatar of finance_teacher
finance_teacher

asked on

Oracle -- update via JOIN ?

How can I change the below to update all 40,000 part numbers ?

update INVENTORY_PART_TAB IPT
set IPT.ABC_CLASS = SCCR.ABC_CLASS
where IPT.PART_NO = (select SCCR.PART_NO from SAB_CYCLE_COUNT_RECLASS SCCR)
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You want to use select first to see that You're updating what You want to update only
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
awking00,

If your merge statement works then the following update (which should be faster than a merge) will also work:

UPDATE
(
SELECT
ipt.abc_class abc_class_old, sccr.abc_class abc_class_new
FROM inventory_part_tab ipt, sab_cycle_count_reclass sccr
WHERE sccr.part_no = ipt.part_no
)
SET abc_class_old = abc_class_new
/

If the above key-preserved update doesn't work, then your merge statement will fail with:

SQL Error: ORA-30926: unable to get a stable set of rows in the source tables.


If that's the case, you'll have to change the merge statement to:


MERGE INTO inventory_part_tab x USING
(
SELECT
max(sccr.abc_class) abc_class_new, sccr.part_no
FROM inventory_part_tab ipt, sab_cycle_count_reclass sccr
WHERE sccr.part_no = ipt.part_no
GROUP BY sccr.part_no
) y
ON( x.part_no = y.part_no)
WHEN MATCHED THEN
UPDATE
SET
x.abc_class = y.abc_class_new
/