Results 1 to 7 of 7

Thread: Oracle sql

  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unhappy Unanswered: Oracle sql

    I am trying to delete some records in an oracle table. The senario is this:
    say for example an employee has 2 records in the table with the same employee status code (FUL), I would like to delete one of the record and leave the other one in the. Any suggestion would be helpful. Straight SQL or PL\SQL Cursor. Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    ex:

    delete from tablename
    where record_id||rowid in (select a.record_id||a.rowid
    from tablename a, tablename b
    where a.loader_unit = b.loader_unit
    and a.record_id = b.record_id
    and a.rowid < b.rowid)

    Join the table to itself ...
    HTH
    Gregg

  3. #3
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Try this:

    DELETE FROM emp_table
    WHERE rowid <> (SELECT MAX(rowid) FROM emp_table a
    WHERE UPPER(a.emp_name) = UPPER(emp_table.emp_name) AND
    UPPER(a.emp_stat_code) = UPPER(emp_table.emp_stat_code));

    Try it on a temp table first, or backup your table...

    Good-luck,
    Rip
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  4. #4
    Join Date
    Mar 2004
    Posts
    3

    QRACLE SQL

    Sorry, I guess I did not word my request very well. What I wanted help with is: To delete the emp_status row if the previous emp_status row has the same client_emp_status_code. for eg if both client_emp_status_code = 'FUL' Thanks again.

  5. #5
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34

    Re: QRACLE SQL

    Originally posted by bscott5
    Sorry, I guess I did not word my request very well. What I wanted help with is: To delete the emp_status row if the previous emp_status row has the same client_emp_status_code. for eg if both client_emp_status_code = 'FUL' Thanks again.
    Record and row are one in the same... It is still confusing, since you state that you want to delete the emp_status row... I believe you mean column or field.

    I hate nitpicking, but we are trying to help here... And, it is hard to answer with incorrect terminology.
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  6. #6
    Join Date
    Jan 2004
    Location
    Germantown, MD
    Posts
    34
    Also, since you don't want to remove rows, delete is the incorrect term. Try update.

    Here is a sample:

    UPDATE emp_table SET client_emp_status_code = NULL
    WHERE rowid <> (SELECT MAX(rowid) FROM emp_table a
    WHERE UPPER(a.emp_name) = UPPER(emp_table.emp_name) AND
    UPPER(a.client_emp_status_code) = UPPER(emp_table.client_emp_status_code));
    "The world is a dangerous place, not because of those who do evil, but because of those who look on and do nothing. "
    - Albert Einstein

  7. #7
    Join Date
    Mar 2004
    Posts
    3

    Re: QRACLE SQL

    Originally posted by the Oracle
    Record and row are one in the same... It is still confusing, since you state that you want to delete the emp_status row... I believe you mean column or field.

    I hate nitpicking, but we are trying to help here... And, it is hard to answer with incorrect terminology.
    Sorry if I was not clear. However, I do mean record. I want to delete the emp_status record if the previous emp_status record has the same clt_emp_status. for eg an employee has 2 records in the emp_status table and both clt_emp-status = "FUL" I would like to delete one of the records and leave the other one. Hopefully this explaination will clear up any confusion. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •