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.