asked on

delete duplicate payments without using a temporary table

CREATE TABLE [dbo].[payments](
[paymentid] [int] IDENTITY(1,1) NOT NULL,
[orderid] [int] NOT NULL,
[dateentered] [datetime] NULL,
[datebilled] [datetime] NULL,
[type] [varchar](50) NOT NULL,
[number] [varchar](80) NOT NULL,
[expiration] [varchar](10) NULL,
[CV2] [int] NOT NULL,
[confirmation] [varchar](70) NOT NULL,
[resultcode] [varchar](40) NOT NULL,
[debit] [money] NOT NULL,
[paymentinfo] [text] NULL,
[ebayquantity] [varchar](25) NULL,
[paymentfee] [varchar](30) NULL,
[email] [varchar](300) NULL,
[business] [varchar](200) NULL,
[wait] [smallint] NULL,
CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED
(
[paymentid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

without using a temporary table,
first select all the payments where orderid, datebilled,type,number,resultcode,debit,paymentfee,email are equal
and then a command to delete all the duplicate payments
an payment.orderid may have 5 duplicate payments, I only want one

devlab2012

delete from Payments where paymentid not in (select min(paymentid) from payments group by orderid, datebilled, type, number, resultcode, debit, paymentfee, email)

Sharath S

try like this.

;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY orderid,datebilled,TYPE,NUMBER,resultcode,debit,paymentfee,email ORDER BY orderid) rn
         FROM   payments) 
DELETE FROM cte 
WHERE       rn > 1

Open in new window

Sharath S

or this.

;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY orderid,datebilled,TYPE,NUMBER,resultcode,debit,paymentfee,email ORDER BY paymentid) rn
         FROM   payments) 
DELETE FROM cte 
WHERE       rn > 1

Open in new window

rgb192

ASKER

both queries return the same amount of rows

how can I be sure that I am not deleting all the payments
because I want to keep one payment

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION