Delete Logic Question - 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

Transact-SQL (2000)

Delete Logic Question

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-10-16 : 14:30:18

Just wanted to run this by the group.

Can SQL or Access perform a function where it will delete duplicates of something and leave only the latest input or transaction?

We have a table where we get duplicate call records on a customer. The customer ID and all other info stays the same and there is one area that is updated with each contact. For customer "A" there might be 5 records of our contacts with them. Is it possible to sort the records and then delete the last 4 so that we only have the latest contact with the customer when we want to clean up the table?

Suggestions, comments, answers!!

Thanks again.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-16 : 14:31:44

[url]http://www.sqlteam.com/item.asp?ItemID=3331[/url]

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-10-16 : 16:46:59

Tara,

Thanks for the point in the right directions.

Thanks again as always.

GC

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-16 : 17:07:42

quote:
Originally posted by Gary Costigan

Suggestions, comments, answers!!

Yeah...how about putting contraints on the table.

That way you won't have this problem again...

[sound of code exploding]
Hey what happened?
[/sound of code exploding]

just better hope the developers have error handling...the data got in there somehow...

Brett

8-)

Mateen
Starting Member

14 Posts

Posted - 2003-10-17 : 05:49:06

Try this:

delete from customer d
where d.trans_date not in ( select y.to_keep_date from

(select customer.cust_id,to_keep_date=min(customer.trans_date) from
customer,
(
SELECT CUST_ID,CUST_NAME,VARIABLE_VALUE,trans_date
FROM CUSTOMER
WHERE CUST_ID IN (SELECT CUST_ID FROM CUSTOMER
GROUP BY CUST_ID HAVING COUNT(CUST_ID)>1))x
where customer.cust_id = x.cust_id
group by customer.cust_id)y)

Sample Data:
cust_id cust_name variable_value trans_date
A A 1 2003-10-01 00:00:00
A A 2 2003-10-02 00:00:00
A A 3 2003-10-03 00:00:00
B B 5 2003-10-04 00:00:00
B B 6 2003-10-05 00:00:00
C C 7 2003-10-06 00:00:00
C C 9 2003-10-07 00:00:00
D D 10 2003-10-08 00:00:00
L L 1 2003-10-09 00:00:00
L L 2 2003-10-10 00:00:00
L L 3 2003-10-11 00:00:00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources