Better query design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Better query design

Author

Topic

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2001-10-17 : 21:17:08

Hello there,

I need to delete rows from a table that have duplicate primary keys. However, the rows are not identical. There is a datetime field which identifies which is the most current record.
Here is the structure of the table. Only the fields that matter, account number and paid through date.

CREATE TABLE CurrentDelinquency
(
ACCT_NUM varchar(14),
PAID_THRU_DT datetime
)

Here is my routine for deleting the older of the duplicate records (sometimes more than two).

1. Create a Cursor that finds duplicate primary keys (ACCT_NUM).

create cursor for

SELECT ACCT_NUM
FROM CurrentDelinquency
GROUP BY ACCT_NUM
HAVING COUNT(ACCT_NUM) > 1

2. loop throgh that cursor and delete the records that are not equal to the MAX(PAID_THRU_DT) for that ACCT_NUM.

DELETE FROM CurrentDelinquency
WHERE ACCT_NUM = @ACCT_NUM
AND PAID_THRU_DT !=
(SELECT MAX(PAID_THRU_DT) FROM CurrentDelinquency WHERE ACCT_NUM = @ACCT_NUM)

There are two things that I do not like about this process. 1. I am using a cursor. 2. I don't like to use subqueries. From what I hear both should be avoided when possible. I would appreciate any help provided.

thanks,

Creighton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources