Red Devil Red Devil - 2 months ago 9
SQL Question

Need to delete multiply records from the table

I have a table called TEST

------------------------------------------
ID name intime
------------------------------------------
1 ABC 2015-09-03 10:00:00
1 ABC 2015-09-03 10:00:00
2 XYZ 2015-09-03 11:00:00
2 XYZ 2015-09-03 11:00:00


My objective is to delete the duplicate records from a table like for ABC intime is duplicate. And i have multiple records not just 4 its just for an example.

I am trying something like this

with cte as
(
select id,InTime ,count(*) as c
from intime as m
where InTime between convert(varchar(10),'2015-09-01 00:01:00',103) and convert(varchar(10),'2015-09-30 00:01:00',103)
group by id,InTime
having count(*) > 1
)
select * from cte
order by ID


Output

id InTime c
----------------------------------
1 2015-09-03 10:00:00 2
2 2015-09-03 11:00:00 2


This query will give me the records which is having multiple intime on same date

with cte as
(
select ID, inTime,
row_number() over (partition by convert(varchar(10),intime,103) order by intime desc) as r
from intime
where ID in (1)
and
inTime between '2015-09-01 00:01:00' and '2015-09-30 23:59:59'
)
select * from cte

delete from cte
where r > 1


And this is the query which will remove the duplicate entry.

Problem which i am facing is I have to enter ID manually 1 by 1, If i do something where ID in (1,2)

It gives me output as :

ID inTime r
2 2015-09-03 11:00:00 1
2 2015-09-03 11:00:00 2
1 2015-09-03 10:00:00 3
1 2015-09-03 10:00:00 4


And then if i delete r>1 it will delete 3 rows.

I want to create something where I can delete all the duplicate records from the table. I am using sql-2008. Any suggestion will be helpful.

Answer

try this

with abc as
  (
  select * ,rn=ROW_NUMBER()over(partition by ID ,name,intime order by columnName)from yourtablename
  )

  delete from abc where rn>1 
Comments