• Shopping cart software
• Outsourcing
• Post Article
• Post Question

How to update multiple values in a table

Hi Experts, 

I have two tables - Master & Temp

Master:

Temp:

B is the primary constraint In both master and temp table. I have to write an update query such that:

1)      When master.A = Temp.A and master.B = Temp.B and nvl(master.C,’0’) <> Temp.C, Update master.C = Temp.C

2)      For master.A = Temp.A and master.B not present in Temp.B and master.C is not null, update master.C to null.

Master table is having around 5000 distinct value for A and around millions of records in total. Temp table will have around 500000 records at a time. Can we have a single Update query for this. I have worked upon a query, but that is taking too long to run:

update Master m

set m.C=

(select distinct Temp.C from Temp

where Temp.B=m.B)

where not exists (

select C from Temp

 where (m.B=Temp.B and

            m.C=Temp.C)

    or (m.A<>Temp.A)

 )

Please assist with your valuable inputs and suggestion.

Thanks,

Kunal