SQL Delete/Update

30 pts.
Tags:
AS/400
SQL
SQL statements
I have file say with 100 records and I have 2 records which have similar data , so I have to delete only one record (out of 2 with same data) from the file, how would I do that? Every value in those records are same. I need solution through SQL statements.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Just change the below statement to use your file and the name of the duplicate field(s)

DELETE FROM CBROWNE/U$LNMACHIN A               
 WHERE RRN(A) >                                 
 (SELECT MIN(RRN(B)) FROM CBROWNE/U$LNMACHIN B  
 WHERE A.MI_PCORD# = B.MI_PCORD#)               

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Joeprior
    Out of 100 records, 2 record are similar but their id number or serial number is not the same so you can use the following SQL code to delete the duplicate record.
    Delete from "table name"
    where employee id="";
    500 pointsBadges:
    report
  • srav520
    Hello Joeprior,
    There are no key fields on the file, all the values in those records are the same.
    30 pointsBadges:
    report
  • philpl1jb

    The RRN( filename ) gets the relative record number of the rows

    example

    Select RRN( myFile ) from myFile where field1 = 'X' and field2 = 'Y'

    and you can use the max or min to get just a single rrn

    Select max(RRN( myFile )) from myFile where field1 = 'X' and field2 = 'Y'

    now it's duck soup

    Delete from myFile

    where RRN(myFile) =

    (Select max(RRN( myFile )) from myFile where field1 = 'X' and field2 = 'Y')


    Does that help

    54,090 pointsBadges:
    report
  • TheRealRaven
    The RRN() function is very useful in DB2 for i. I'd possibly use it because it's easy. If we knew what version you were running, we might have other suggestions that are more useful in general.
    35,660 pointsBadges:
    report
  • Joeprior
    Hello srav520
    Did you have assign a unique key or primary key to the table?
    500 pointsBadges:
    report
  • philpl1jb

    I think you can find duplicate records with something like this

    Select max(RRN(myFile)) from myFile

    Group by fld1, fld2, fld3, fld4

    Having count(*) > 1

    54,090 pointsBadges:
    report
  • srav520
    Hi Philpl1jb,

    The below SQL which you have provided helped.... thank you..

    Select RRN( myFile ) from myFile where field1 = 'X' and field2 = 'Y'

    and you can use the max or min to get just a single rrn

    Select max(RRN( myFile )) from myFile where field1 = 'X' and field2 = 'Y'

    @Joeprior, There are no unique or primary key constraints on the file, current version we use is V7R1.... Thank you for your response.... appreciate it..

    30 pointsBadges:
    report
  • TheRealRaven
    It's hard to be sure without knowing your table and column names; but using your example names, experiment with something like this:
    select * from 
       ( SELECT a.*, 
                ROW_NUMBER() OVER( ORDER BY ORDER OF a ) as RN 
         FROM myFile a where field1 = 'X' and field2 = 'Y' ) as myRN 
    That avoids the RRN() function, so it's closer to what might work in other versions of DB2 and in other DBMSs. You should be able to see how you can reference RN to direct a DELETE statement.
    35,660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: