How to filter and delete duplicated deposit record for Sales Order ?

Created by QNE Software Sdn bhd, Modified on Tue, 7 Mar, 2023 at 11:03 AM by QNE Software Sdn bhd

Scenario 

When the server or the network environment often experience unstable connections, there are chances that data updates to the server are incomplete or doubled.

Instead of checking Sales Order 1 by 1, you can filter them through SSMS with a script. 

Solution 

1. Launch SSMS and connect to your database.

https://support.qne.com.my/a/solutions/articles/81000385373

2. Click “New Query” button.

3. Copy and paste below query to filter all duplicated Sales Order’s deposit.

select *

from   (select (select salesordercode from salesorders where id = salesdepositlink.salesorderid)as SOCode,*,

row_number() over (partition by depositid,salesorderid,depositamount,depositamountlocal order by id desc) [row] 

from    salesdepositlink) duplicated 

where   [row] > 1

4. Click “Execute” button.

5. The “Result” tab will filter all those duplicated Sales Order’s Deposit record.

 

6. Click “New Query” button again.

7. Copy and paste below query to delete all duplicated Sales Order’s deposit.

delete duplicated

from   (select *, row_number() over (partition by depositid,salesorderid,depositamount,depositamountlocal order by id desc) [row] 

from    salesdepositlink) duplicated 

where   [row] > 1

 

8. Click “Execute” button and all the duplicated Sales Order’s deposit will be deleted.

** Learn more about Azure Cloud

** Learn more about QNE Hybrid Cloud Software



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article