updating multiple columns at once (variation)

Question

I am trying to update multiple columns in Oracle and after going through a few scenarios settled on the code below.

My problem is that the more streamlined code is not updating what i need while the more complicated one does - i am trying to attain this via minimal processing power as i'm looking at hundreds of thousands of updates or even more.

So the first code:

UPDATE table@database1 i2 
SET    (i2.access, i2.permission) = 
(select 
i2m.access, i2m.permission 
from temporarytable ss
    JOIN table2 pgm 
     ON ss.secgroup = pgm.string 
   JOIN table i2m 
     ON pgm.hmy = i2m.hgroup 
        AND ss.pername = i2m.sobjname 
   JOIN table2@database1 pg 
     ON ss.secgroup = pg.string 

    WHERE 
    pg.string = 'string'  -- this limits the updates to a specific subset of data
    and i2m.hmy > 0 -- this for some freak records in both tables that are missing a primary key
    and pg.hmy = i2.hgroup -- this matches the key of the 'string' from above to the records i need to update
    and ss.pername = i2.sobjname -- further condition on which records to update. so only the ones that match from the temp table to the target table
    and ss.orig_hmy = i2.hmy) -- further condition to make sure i am updating only records matching between temp table and target table

Now, if I run this, instead of updating only about 700 records that match the above subquery it updates all records from table 'table@database1' and I can't see why (probably one of those things that I don't get about Oracle :) )

But if I run the below - which the only difference is that I insert the whole subquery in a 'where exists' - then this does update only what I need. My issue is that the way I understand it, the subquery is run twice - once in the update and once in the where clause - which I would say is a waste of processing power.

UPDATE table@database1 i2 
SET    (i2.access, i2.permission) = 
(select 
i2m.access, i2m.permission 
from temporarytable ss
    JOIN table2 pgm 
     ON ss.secgroup = pgm.string 
   JOIN table i2m 
     ON pgm.hmy = i2m.hgroup 
        AND ss.pername = i2m.sobjname 
   JOIN table2@database1 pg 
     ON ss.secgroup = pg.string 

    WHERE 
    pg.string = 'string'
    and i2m.hmy > 0
    and pg.hmy = i2.hgroup
    and ss.pername = i2.sobjname
    and ss.orig_hmy = i2.hmy)

where exists (select 
i2m.access, i2m.permission 
from temporarytable ss
    JOIN table2 pgm 
     ON ss.secgroup = pgm.string 
   JOIN table i2m 
     ON pgm.hmy = i2m.hgroup 
        AND ss.pername = i2m.sobjname 
   JOIN table2@database1 pg 
     ON ss.secgroup = pg.string 

    WHERE 
    pg.string = 'string'
    and i2m.hmy > 0
    and pg.hmy = i2.hgroup
    and ss.pername = i2.sobjname
    and ss.orig_hmy = i2.hmy)

note: In case it doesn't show, I have multiple DBs with the same schema. I am trying to update a table across DBs with the information from a master schema. The Temp table acts as a repository for the records that are different and need updating - no reason in updating millions of records if only 15% differ from the master schema.

You can use merge if you don't like this method of updating tables. — Gordon Linoff
– Gordon Linoff, Commented Jul 28, 2016 at 22:14
I agree with Gordon, please do read and familiarize yourself with the MERGE approach. It will pay big dividends in the future. — user5683823
– user5683823, Commented Jul 28, 2016 at 22:47
" it updates all records from table 'table@database1' and i can't see why." Because you do not have a where clause on the update statement. Nothing Oracle specific about that, in all platforms I'm familiar that with update without where updates every row. — Shannon Severance
– Shannon Severance, Commented Jul 28, 2016 at 23:01
MERGE does seem to be a better way at doing it. Will look into adapting the update into a MERGE and see where that takes me. If it works as expected i will get back and confirm answer. *not sure what my reason was initially not to go this route — EkeshOkor
– EkeshOkor, Commented Jul 29, 2016 at 0:17
Ok. So MERGE did the trick. Now i just need to know why DBMS says 842 rows affected while the DB actually has only 753 affected records. — EkeshOkor
– EkeshOkor, Commented Jul 29, 2016 at 0:54

EkeshOkor · Accepted Answer · 2016-07-29 01:04:14Z

1

After taking suggestions from all the helpful people over here i looked into using MERGE and the above query was adapted into the below - which proved to be successful!

    MERGE INTO table@database1 i2 
    USING (
    select i2m.access, i2m.permission, ss.orig_hmy
    from table i2m
        JOIN table2 pgm 
         ON i2m.hgroup = pgm.hmy 
       JOIN temporarytable ss
         ON pgm.string = ss.string 
            AND ss.pername = i2m.sobjname 
       JOIN table2@database1 pg 
         ON ss.string = pg.string 
    WHERE 1 = 1
        and i2m.hmy > 0 
        and pg.string = 'string'
        and ss.database = 'database1' 
    ) u on (i2.hmy = u.orig_hmy)
    WHEN MATCHED THEN
        UPDATE SET i2.access = u.access, i2.permission = u.permission;

Many thanks to all!

answered Jul 29, 2016 at 1:04

EkeshOkor

1092 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

updating multiple columns at once (variation)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related