asked on

Delete Query for Duplicate records

hi ,

Due to some latency issues in the network some duplicate records are getting inserted in our T_MONTHLY_INCOME table .
So we are running below query to get duplicate records and checking the amounts manually and deleting row by row .
If i say duplicate record either its having same amount(INCOME_AMOUNT)twice or the amount is 0.0 for the same income code .

Can we create a delete query which will identify the duplicate records and delete those ?

SELECT COUNT(*), i.loan_id,i.INCOME_AMOUNT,
i.INCOME_CODE
FROM UW.T_MONTHLY_INCOME i
join WC.LOANS l on l.loan_id = i.loan_id
where
i.loan_id = 1234567 and
i.INCOME_CODE in ('08','09','10','11','12','13','AB')
GROUP BY i.loan_id, i.INCOME_CODE,INCOME_AMOUNT
having count(*) > 1

slightwv (䄆 Netminder)

Do you need the join to LOANS to identify the duplicates? I'm not seeing the need.

If not, there are several delete duplicates out there. I would start by looking at:
https://www.experts-exchange.com/discussions/220821/4-June-2018-Release-Notes.html

delete from t 
where rowid IN ( select rid 
from (select rowid rid, 
row_number() over (partition by 
companyid, agentid, class , status, terminationdate 
order by rowid) rn 
from t) 
where rn <> 1;

Open in new window

I can try and guess at a delete statement using your example above but have no way to actually test it.

If you can provide some sample rows and expected results, we can post tested SQL.

Vinoy K P

ASKER

Here i am providing some sample and expectation .
Scnario 1
Loan Id RecordId SSN        Income_code Amount InsertBy Date UpdateBy UpdateDate
123456789      6            XXXXXXX91      17                  100        vinoy      19-JUN-18      vinoy      19-JUN-18
123456789      14            XXXXXXX91      17                  0        vinoy      19-JUN-18      vinoy      19-JUN-18
Expected is just remove the record which is having amount 0.

Scnario 2
Loan Id RecordId SSN        Income_code Amount InsertBy Date UpdateBy UpdateDate
123456789      6            XXXXXXX91      18                  100        vinoy      19-JUN-18      vinoy      19-JUN-18
123456789      14            XXXXXXX91      18                  100        vinoy      19-JUN-18      vinoy      19-JUN-18
Expected is just remove any one record since both are having same amount.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vinoy K P

ASKER

Looks like it may work , but have couple of questions .
1)How or where we need to add the condition to consider the INCOME_CODE , duplicate records are for each INCOME_CODE.
2)How we are ensuring that it will leave the non zero amount ?

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vinoy K P

ASKER

I totally get it now . Working like a Charm . A quick question, this solution work even if i need to join something to the table tab1 ?

slightwv (䄆 Netminder)

>>this solution work even if i need to join something to the table tab1 ?

It should as long as you return tab1's rowid. Always run the query first to make sure you have it right before putting the delete around it.

Vinoy K P

ASKER

Great , Everything works as expected . Thank you.