By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

crazy statement :) please help

P: 6
Hi all,
This is my first time... and i'm not an sql guy by any means, but i can insert, update, blah blah...
I have a question to ask, i have a table that has
UniqueInt | Date1 | int1 | Date2 | varchar 1

I have duplicate records that i need to delete and i need to write an sql statement.

it goes like this,

UniqueInt | Date1 | int1 | Date2 | varchar 1
1 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:00:00 am| 123456
2 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:30:00 am| 123456

delete the first record where int1 =1 and varchar1 is the same and the difference between date2 is less than 1 minute.

Is that possible?

Thanks in advance
Jan 29 '08 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
is something like this what you are after

Expand|Select|Wrap|Line Numbers
  1. DELETE 
  2. FROM TableName 
  3. WHERE DATEDIFF(mi,Date1,Date2)<1
  4.  
hope it helped
Jan 30 '08 #2

P: 6
is something like this what you are after

Expand|Select|Wrap|Line Numbers
  1. DELETE 
  2. FROM TableName 
  3. WHERE DATEDIFF(mi,Date1,Date2)<1
  4.  
hope it helped
Thanks Delerna,
here you are comparing date1 and date2, what i need is to compare date2 from one record and date 2 from another record.
Jan 30 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Hi all,
This is my first time... and i'm not an sql guy by any means, but i can insert, update, blah blah...
I have a question to ask, i have a table that has
UniqueInt | Date1 | int1 | Date2 | varchar 1

I have duplicate records that i need to delete and i need to write an sql statement.

it goes like this,

UniqueInt | Date1 | int1 | Date2 | varchar 1
1 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:00:00 am| 123456
2 | 1/1/2008 10:00:00 am | 1 | 12/15/2007 10:30:00 am| 123456

delete the first record where int1 =1 and varchar1 is the same and the difference between date2 is less than 1 minute.

Is that possible?

Thanks in advance
assumption: there are only two records. if this assumption is wrong, this query will not work:

1. add a new column on your table C(1)
2. update mytable
set newcolumn = '1'
from
(select varchar1, mindate
(select varchar1, min(Date2) as mindate from yourtable where int1 = 1 group by varchar1 ) FirstRow inner join
(select varchar1, max(Date2) as maxdate from yourtable where int1 = 1 group by varchar1 ) SecondRow on FirstRow.varchar1 = SecondRow.varchar1
where abs(datediff(mi, mindate, maxdate)) < 1) summary
where mytable.varchar1 = summary.varchar1 and mytable.date2 = summary.mindate and int1 = 1

3. select * from mytable where newcolumn = '1'. inspect if you flag the right rows.
4. delete from mytable where newcolumn = '1'

you did this because if your query failed, you might delete the wrong row and you don't have a backup table. if you have to rerun your query, you have to replace back all fields to NULL or '0' to reset it.

-- ck
Jan 30 '08 #4

ck9663
Expert 2.5K+
P: 2,878
and by the way, there's always cursor

-- ck
Jan 30 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.