few rows update too slowly if table is big 1

[amortillaro]

Hi guys: I have this query:

UPDATE EachPercent
SET (BlankPercentAlloc) = (
Select PercentAlloc From AllPercents
WHERE (EachPercent.Lx_Date >=
To_Date('01/04/2004', 'MM/DD/YYYY'))
And (EachPercent.Lx_Date <=
To_Date('01/18/2004', 'MM/DD/YYYY'))
AND (EachPercent.Lx_SeqNum = AllPercents.SeqNum)
);

The table EachPercent have about 100k records, and when the statement is processed I receive the msg that 100k rows processed.
How can I rewrite the update query in order to limit the processed rows to the ones who meet the Lx_Date condition?

 

[Turkbear]

Hi,
Your sql will update ALL the rows in EachPercent..

To limit this to what it appears you want :

UPDATE EachPercent
SET (BlankPercentAlloc) = (
Select PercentAlloc From  AllPercents
 WHERE (EachPercent.Lx_Date >= 
   To_Date('01/04/2004', 'MM/DD/YYYY')) 
  And (EachPercent.Lx_Date <= 
   To_Date('01/18/2004', 'MM/DD/YYYY'))
  AND (EachPercent.Lx_SeqNum = AllPercents.SeqNum)
    )
WHERE (EachPercent.Lx_Date >= 
   To_Date('01/04/2004', 'MM/DD/YYYY')) 
  And (EachPercent.Lx_Date <= 
   To_Date('01/18/2004', 'MM/DD/YYYY'))
  AND (EachPercent.Lx_SeqNum = AllPercents.SeqNum)

;

With the added where clause the only records in EachPercent that will be updated will be those that match the criteria in your subquery..I'm assuming those are the ones you want to update.

 

[amortillaro]

Then the where part in the subquery does not play any role?.
If I'm ok then the result query will be

UPDATE EachPercent
SET (BlankPercentAlloc) = (
Select PercentAlloc From AllPercents
WHERE (EachPercent.Lx_SeqNum = AllPercents.SeqNum)
)
WHERE (EachPercent.Lx_Date >=
To_Date('01/04/2004', 'MM/DD/YYYY'))
And (EachPercent.Lx_Date <=
To_Date('01/18/2004', 'MM/DD/YYYY'))
AND (EachPercent.Lx_SeqNum = AllPercents.SeqNum)
;

Let me know... Thanks

 

[amortillaro]

Or even more short...

UPDATE EachPercent
SET (BlankPercentAlloc) = (
Select PercentAlloc From AllPercents
WHERE (EachPercent.Lx_SeqNum = AllPercents.SeqNum)
)
WHERE (EachPercent.Lx_Date >=
To_Date('01/04/2004', 'MM/DD/YYYY'))
And (EachPercent.Lx_Date <=
To_Date('01/18/2004', 'MM/DD/YYYY'))
;

Thanks, is working great now... Aishel