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