SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help required with Delete logic
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

clinton_eg
Yak Posting Veteran

India
60 Posts

Posted - 05/13/2013 :  09:31:25  Show Profile  Reply with Quote
Hi

I have data in the following format. And data get inserted into the table on a daily basis.

Name |Age |City |DOB |InsertDate
Tom |21 |Austin |4/30/2000 |5/1/2013
Brian |30 |Sydney |6/2/1985 |5/2/2013
Brian | |Sydney | |5/3/2013
Brian | |Sydney | |5/4/2013
Brian | |Sydney | |5/5/2013
Charlie | |New York | |5/10/2013
Charlie | |New York | |5/11/2013
Charlie | |New York | |5/12/2013

Scenario 1: The entry for Brian, in this case there is one complete record and the other entries which got added on 3rd, 4th and 5th May do not have the Age and DOB.
Requirement: I want to delete all entries in the table except for the 2nd May record.

Scenario 2: The entries for Charlie are all the same for records inserted 10th to 12th May.
Requirement: I want to delete all records of Charlie except for the record with the min insert date (10th May 2013)

Could you advise how do I achieve this with a single delete statement, considering that the combination of Name,City & InsertDate is the PK columns.

Thanks

Ewan Gilby

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 05/13/2013 :  10:18:59  Show Profile  Reply with Quote
Hi,

;with P
as 
(
select *,
 ROW_NUMBER() OVER (partition by Name order by Age desc ,DOB desc , INsertDate asc) as rw
from Person)


delete 
from P
where P.rw<>1
		and P.Age is null
		and P.DOB is null


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/14/2013 :  00:36:58  Show Profile  Reply with Quote
Can there be a case where there multiple records with Age and DOB fields having non empty values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

clinton_eg
Yak Posting Veteran

India
60 Posts

Posted - 05/14/2013 :  05:39:02  Show Profile  Reply with Quote
visakh16 - yes there can be multiple rows with Age and Dob having non empty values.. what I gave is just an example, however the volumes are in millions.

stepson - Thanks for the details.

How do I avoid inserting a duplicate record, if it come in the next days data load?

Ewan Gilby
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/14/2013 :  05:53:50  Show Profile  Reply with Quote
quote:
Originally posted by clinton_eg

visakh16 - yes there can be multiple rows with Age and Dob having non empty values.. what I gave is just an example, however the volumes are in millions.

stepson - Thanks for the details.

How do I avoid inserting a duplicate record, if it come in the next days data load?

Ewan Gilby


Then previous suggestion wont work fine in those cases

you need a slight tweak then
use like

DELETE t
--SELECT *
FROM
(
 select  ROW_NUMBER() OVER (partition by Name order by Age desc ,DOB desc , INsertDate asc) as rw
--,*
from Person
)t
WHERE rw > 1


First do select after uncommenting the commented code and once happy revert to original above suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/14/2013 :  05:56:33  Show Profile  Reply with Quote
quote:
Originally posted by clinton_eg

visakh16 - yes there can be multiple rows with Age and Dob having non empty values.. what I gave is just an example, however the volumes are in millions.

stepson - Thanks for the details.

How do I avoid inserting a duplicate record, if it come in the next days data load?

Ewan Gilby


For that add a not exists check in your insert statement logic like

INSERT table (Name,Age,City ,...)
SELECT Name,Age,City,..
FROM sourcetable s
WHERE NOT EXISTS (SELECT 1
                  FROM table
                  WHERE Name = s.Name
                  AND Age = s.Age
                  AND DOB = s.DOB
                  )


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000