Home » SQL & PL/SQL » SQL & PL/SQL » Delete Statement (PL/SQL, Oracle 10g)
Delete Statement [message #362908] Thu, 04 December 2008 08:26 Go to next message
rchenna
Messages: 25
Registered: February 2008
Junior Member
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
Re: Delete Statement [message #362910 is a reply to message #362908] Thu, 04 December 2008 08:32 Go to previous message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
You may want to try this DELETE:
delete FROM fr_audit_emp_bonuses a
WHERE a.rowid > (
SELECT MIN(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);


Thanks,
Jim
Previous Topic: Error in insert query.
Next Topic: How to send mail through pl/sql
Goto Forum:
  


Current Time: Fri Dec 09 19:15:24 CST 2016

Total time taken to generate the page: 0.08943 seconds