Home » SQL & PL/SQL » SQL & PL/SQL » sorting and query tuning
sorting and query tuning [message #252232] Wed, 18 July 2007 04:34 Go to next message
yog_23
Messages: 79
Registered: March 2007
Member
I want to flag duplicates in a table.

RECORD_NO is the primary key and a sequence number

Duplicates are possible in field DOC_ID, for different dates

What I want is flag all the records as dups except the max date. Is there a better way to write this update. Its currently produces error ORA-01652 ..unable to extend temp segment. The table is over a million records..

RECORD_NO     DOC_ID       EFF_DT    DUPLICATE_FLAG 
1              100        1/1/2006        
2              100        1/1/2007        
3              200        1/1/2006        
4              300        1/1/2006        
5              300        1/1/2007        
6              300        6/1/2007        


    UPDATE
        TABLE_INPUT inp1
    SET 
        DUPLICATE_FLAG  = 1
    WHERE
        EFF_DT <= (
        SELECT 
            MAX(EFF_DT)
        FROM 
            TABLE_INPUT inp2 
        WHERE       
             inp1.DOC_ID                 = inp2.DOC_ID 
             AND inp1.EFF_DT            <= inp2.EFF_DT
             AND inp1.RECORD_NO         < inp2.RECORD_NO
        );
Re: sorting and query tuning [message #252243 is a reply to message #252232] Wed, 18 July 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
merge into TABLE_INPUT a
using (select rowid row_id,
              row_number() over(partition by doc_id order by eff_dt desc) rn
       from table_input) b
on (b.row_id=a.rowid and b.rn > 1)
when matched then update set DUPLICATE_FLAG=1
/

Regards
Michel

[Updated on: Wed, 18 July 2007 14:43]

Report message to a moderator

Re: sorting and query tuning [message #252388 is a reply to message #252232] Wed, 18 July 2007 14:25 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
Hi Michel,

Thank you for the help...

Its giving a syntax error (missing paranthesis).not sure where it goes...Any help..

Thanks
Yog
Re: sorting and query tuning [message #252391 is a reply to message #252232] Wed, 18 July 2007 14:29 Go to previous messageGo to next message
yog_23
Messages: 79
Registered: March 2007
Member
figured it out..thank you..
Re: sorting and query tuning [message #252402 is a reply to message #252391] Wed, 18 July 2007 14:45 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A ")" was missing, I fix it.
It would be great if you did it (posted the correct query).
You are not the only one to be interested in the question.

Regards
Michel
Previous Topic: 'records delimited by' in external tables
Next Topic: Updating table with existing table data
Goto Forum:
  


Current Time: Fri Dec 09 12:04:26 CST 2016

Total time taken to generate the page: 0.07577 seconds