How come this delete statement doesn't work ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How come this delete statement doesn't work ?

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-29 : 21:59:23

why doesn't this delete statement work?

delete
from deleteme2 a, (select min(date) mindate, max(date) maxdate, vin from deleteme2 group by vin)b
where a.vin = b.vin and a.date<>b.mindate and a.date <>b.maxdate

When its a select* statement it works.

basically I have entries with 2 columns, vin and date

There are many many many identical vins but none with the same date.

I want to delete every date in between and keep only the oldest date, and the newest date for each corresponding vin.


vin	date
a	1/2/2008
a	1/3/2008
a	1/4/2008
a	1/5/2008
b	1/6/2008
b	1/7/2008
b	1/8/2008
b	1/9/2008

Therefore i need to write a delete statement that will get rid of all the entries with dates that are in between.


vin	date
a	1/2/2008
a	1/5/2008
b	1/6/2008
b	1/9/2008

like the above. There should be 2 entries for each vin, one with the most recent date, and one with the oldest.

jason7655
Starting Member

24 Posts

Posted - 2008-12-29 : 22:57:35

I would try to use between min and max.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2008-12-29 : 23:37:02

Hi,Try This

delete
from dup
where dup.date IN (select b.date from
(select vin,min(date) mindate, max(date) maxdate from dup group by vin) a
cross apply (select * from dup where dup.date <> mindate and dup.date <> maxdate and vin = a.vin) b)

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-29 : 23:41:58

declare @tab table(vin varchar(32), date datetime )
insert into @tab
select vin,max(date) from urtable group by vin
union all
select vin, min(date) from urtable group by vin
delete from urtable where date NOT IN (select date from @tab)
select * from urtable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:23:42

quote:
Originally posted by sqlchiq

why doesn't this delete statement work?

delete
from deleteme2 a, (select min(date) mindate, max(date) maxdate, vin from deleteme2 group by vin)b
where a.vin = b.vin and a.date<>b.mindate and a.date <>b.maxdate

When its a select* statement it works.

basically I have entries with 2 columns, vin and date

There are many many many identical vins but none with the same date.

I want to delete every date in between and keep only the oldest date, and the newest date for each corresponding vin.

vin date
a 1/2/2008
a 1/3/2008
a 1/4/2008
a 1/5/2008
b 1/6/2008
b 1/7/2008
b 1/8/2008
b 1/9/2008

Therefore i need to write a delete statement that will get rid of all the entries with dates that are in between.

vin date
a 1/2/2008
a 1/5/2008
b 1/6/2008
b 1/9/2008

like the above. There should be 2 entries for each vin, one with the most recent date, and one with the oldest.


DELETE t
FROM YourTable t
LEFT JOIN (SELECT vin,max(date) as latest
           FROM YourTable
           GROUP BY vin)l
ON l.vin=t.vin
AND l.latest=t.date
LEFT JOIN (SELECT vin,min(date) as oldest
           FROM YourTable
           GROUP BY vin)o
ON o.vin=t.vin
AND o.oldest =t.date
WHERE l.vin IS NULL
AND o.vin IS NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:25:32

and if sql 2005, you can use ROW_NUMBER also to delete


DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY vin ORDER BY date) AS Inc,ROW_NUMBER() OVER (PARTITION BY vin ORDER BY date DESC) AS Dec
FROM YourTable)t
WHERE t.Inc<>1 AND t.Dec <>1

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-30 : 12:11:43

quote:


DELETE t
FROM YourTable t
LEFT JOIN (SELECT vin,max(date) as latest
           FROM YourTable
           GROUP BY vin)l
ON l.vin=t.vin
AND l.latest=t.date
LEFT JOIN (SELECT vin,min(date) as oldest
           FROM YourTable
           GROUP BY vin)o
ON o.vin=t.vin
AND o.oldest =t.date
WHERE l.vin IS NULL
AND o.vin IS NULL

one more problem, the dates are all random, for example...

1/25/2008 12:45:23
1/25/2008 11:22:12
1//12/2008 23:22:11

Is there a way to sort this change this to

1/25/2008 00:00:00
1/25/2008 00:00:00
1//12/2008 00:00:00

before, during, and after the delete statement?

I do not believe that the following is valid

min(dateadd(dd, datediff(dd, 0, YourDateHere), 0))

and

max(dateadd(dd, datediff(dd, 0, YourDateHere), 0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 12:14:53

did understand why that becomes a problem? dont you just need to delete all execept latest and oldest ones, which will work fine even in above case as max() and min() returns correct values irrespective of actual order of data.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-30 : 13:34:14

quote:
Originally posted by visakh16

did understand why that becomes a problem? dont you just need to delete all execept latest and oldest ones, which will work fine even in above case as max() and min() returns correct values irrespective of actual order of data.

Really? will it work correctly as is?

I thought SQL server does max and min by the absolute first number, for example in a max,

9/12/2008 11:12:23 might be read as later than 10/1/2008 12:22:12 because the 1 in 10 is less than the 9

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 13:38:17

quote:
Originally posted by sqlchiq

quote:
Originally posted by visakh16

did understand why that becomes a problem? dont you just need to delete all execept latest and oldest ones, which will work fine even in above case as max() and min() returns correct values irrespective of actual order of data.

Really? will it work correctly as is?

I thought SQL server does max and min by the absolute first number, for example in a max,

9/12/2008 11:12:23 might be read as later than 10/1/2008 12:22:12 because the 1 in 10 is less than the 9

it will sort alphabetically based on data type and then take max. so if datatype of field is datetime or smalldatetime, then max() will always return 10/1/2008 12:22:12 as Oct comes after sept while sorting dates.
However if your data type happens to be char or varchar, it works exactly as you told.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-12-30 : 13:42:56

I see, no wonder my max's and min's were screwed up.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 13:44:40

quote:
Originally posted by sqlchiq

I see, no wonder my max's and min's were screwed up.

thats the reason why its always suggested to use proper datatypes for fields. making your date fields as datetime type will make manipulations and calculations much easier, as sqlserver has several date functions to help you in it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources