Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    9

    Unanswered: Delete Duplicate Records

    Take a look at these statements

    --
    SELECT COUNT(*)
    FROM (
    SELECT a.person_seq_id, a.action_type, a.field_name, a.old_value, a.new_value, a.created_by, a.period, a.created_dt, COUNT(*)
    FROM fr_audit_emp_bonuses a
    GROUP BY a.person_seq_id, a.action_type, a.field_name, a.old_value, a.new_value, a.created_by, a.period, a.created_dt
    HAVING COUNT(*) >1) b
    --
    The above statement gives around 100K duplicate records.
    --
    delete FROM fr_audit_emp_bonuses a
    WHERE a.rowid > ANY (
    SELECT b.rowid FROM fr_audit_emp_bonuses b
    WHERE a.person_seq_id = b.person_seq_id AND a.action_type = b.action_type
    AND a.field_name = b.field_name AND a.old_value = b.old_value
    AND a.new_value = b.new_value AND a.created_by = b.created_by
    AND a.created_dt = b.created_dt AND a.period = b.period)
    --
    The above statement only deletes 6 records. I expect it to delete the duplicate it found from the 1st statement.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system, please? oracle? informix? db2?

    100K duplicate employee bonuses? whoa!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    9
    Sorry. It is Oracle 10g

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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