Help required with Delete logic

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 2012 Forums

Transact-SQL (2012)

Help required with Delete logic

Author

Topic

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2013-05-13 : 09:31:25

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-05-13 : 10:18:59

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 00:36:58

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

clinton_eg
Yak Posting Veteran

60 Posts

Posted - 2013-05-14 : 05:39:02

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 05:53:50

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 05:56:33

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources