Member Avatar for pssingh1001

How to delete duplicate record in sql serer
plz suggest me............

thanks

  • 3 Contributors
  • 9 Replies
  • 193 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by pssingh1001

Dani AI

Generated

Given the clarification from that Table1 contains duplicate id values (id = 2 appears twice), and the earlier reminders from and about needing the table structure, here is a concise, practical approach that is safe to run and easy to adapt.

Start by identifying and inspecting duplicates (always test first and back up or run inside a transaction):

SELECT id, COUNT(*) AS dup_count
FROM Table1
GROUP BY id
HAVING COUNT(*) > 1;

SELECT *
FROM Table1
WHERE id IN (SELECT id FROM Table1 GROUP BY id HAVING COUNT(*) > 1)
ORDER BY id;

Decide which row to keep for each duplicate group (latest, earliest, smallest PK, etc.). If there is a surrogate key or timestamp, use it to control which row survives. If no reliable column exists, add a temporary identity to choose one deterministically.

Common, robust delete pattern using a CTE + ROW_NUMBER (replace PKColumn with the column that should determine the survivor):

WITH CTE AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY PKColumn) AS rn
  FROM Table1
)
DELETE FROM CTE
WHERE rn > 1;

If no PKColumn exists, add a temp identity, run the CTE delete using ORDER BY tmpId, then drop the temp column.

After duplicates are removed, enforce uniqueness to prevent recurrence:

ALTER TABLE Table1
ADD CONSTRAINT UQ_Table1_id UNIQUE (id);

Notes and cautions: always preview the rows that would be deleted by selecting WHERE rn > 1 before issuing DELETE; for very large tables consider batching or rebuilding with SELECT DISTINCT into a new table; if duplicate rows contain differing data, consider merging rather than blind deletion.

Recommended Answers

All 9 Replies

Member Avatar for pritaeas

Hard to suggest without more information about your table structure and data.

Member Avatar for radhakrishna.p

the following link will be helpful when your table has no key for any any column

http://www.codeproject.com/Tips/159881/How-to-remove-duplicate-rows-in-SQL-Server-2008-wh

as pritaeas said

its very difficult to give the answer for your requirement without knowing the structure of the table

please provide the table stucture once so that we are here to help you

happy coding

Edited by radhakrishna.p because: getting clarity on the requirement
Member Avatar for pssingh1001

Sorry for waiting..........

Table 1
id  name    city
 1  john    abc
 2  raj      ab

Table 2
id  name    dob
2   raj     ab
3   Mathu   abc

this is the table structure

Member Avatar for pssingh1001

I am waiting for your response ...........

Member Avatar for pritaeas

And what is it that you want as output?

Member Avatar for radhakrishna.p

hai pssingh1001,

please be more informative in your requirement (i.e what do you want and what do you have so far)

explain briefly so that we will help you

Edited by radhakrishna.p because: getting clarity on the problem
Member Avatar for pssingh1001 
I am extremely sorry   .......All.
I was going to wrong side...
this is the table structure ,the Id field 2 is repeted twice .
   Table 1
    id  name city
    1  john  abc
    2  raj   ab
    2  raj   ab
    3  Mathu abc


    then how to delete its duplicacy....
    plz suggest....
Member Avatar for radhakrishna.p

hai pssingh1001,

have you gone through the URL which i have posted initially?( see top most one)

thats help you a lot for this kind of requirement

please check its one and let me know the status

happy coding

Member Avatar for pssingh1001

Hi All.......................
thanks the above mentioned kinks are suitable for me///
If do u have other links , I will prefer.............

Thanks for sharing yr view...........

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.