I want to delete from a table the entries where it has multiple values for Date field.
So say I have Employee table - Id,Name,Date,Points
i want to delete the entries with same Date field which should be unique...just to cleanup
i need to just keep a single entry for date and delete the rest...maybe keep the recent one if possible....can anyone please suggest a update query to do this?
views:
73answers:
4
+1
A:
If your table has primary key, you can join the table to itself by the dup condition and filter out greater PKs, something like the following:
delete e2
from Employee e
join Employee e2 on e.Date=e2.Date
where e.ID < e2.ID
Codism
2010-06-23 21:09:34
+4
A:
Use:
DELETE FROM EMPLOYEE
WHERE id NOT IN (SELECT MAX(e.id)
FROM EMPLOYEE e
WHERE e.date = date
GROUP BY e.date)
The GROUP BY isn't likely to be necessary due to the WHERE clause & only returning one column with an aggregate function on it, but it's included to be safe. This is assuming that when the OP says date field, being SQL Server 2008 that means the DATE data type, not DATETIME.
OMG Ponies
2010-06-23 21:19:02
Are you sure you don't need a table alias to refer to the employee from the outer query? (That is, won't `date` be incorrectly interpreted to mean `e.date`?)
meriton
2010-06-23 21:23:04
@meriton: If there's no table alias, the columns without an alias are assumed to be attached to that table. Aliasing the main table to a delete statement isn't consistently supported, so I don't do it by habit.
OMG Ponies
2010-06-23 21:24:58
I see, good to know :)
meriton
2010-06-23 21:26:39
Looks like that would work, then you can create a trigger with the same SQL to clear out old records upon insertion of a new one with the same date. This way your database is kept up-to-date without having to do a manual maintenance task.
Brian T Hannan
2010-06-23 21:38:17
I don’t think grouping the date is a wise idea, because you have different name under the same date first and each date has a sec part and even millisecond. Try to group by the name
Waleed A.K.
2010-06-23 21:40:04
@Waleed A.K.: Truthfully, we're running on really vague info. The requirement from the OP is clearly the "recent date record kept"; name could be any text, id is more likely of the two to be an identity value.
OMG Ponies
2010-06-23 21:42:39
A:
you can use
DELETE
From Employee
WHERE ID not in (select max(ID) from Employee group by Name)
if the last entry is the recent date, Or you can use this code
DELETE
From Employee
WHERE ID not in
(select max(ID) from Employee e1
where Date=(select max(Date) From Employee where Name=e1.Name)
group by Name)
Waleed A.K.
2010-06-23 21:26:50
+2
A:
this query looks at records with same Id,Name and Points and deletes all but the latest
with cte as(
select id,Name, Date, Points,row_number() over(
partition by id,name,points order by date desc) as ind
from emp)
delete from cte where ind>1
josephj1989
2010-06-23 21:32:55