How to delete row one by one in SQL server 2005.

Question

0.00/5 (No votes)

See more:

I am developing windows application using database purpose sql server 2005, but in my data base some duplicates records are stored. How to delete row one by one row in sql server 2005. Here I am not use in primary key.
Ex:
Stuid stuname add1 add1 phone
1 A Test Test 123
2 B abc def 456
1 A Test Test 123
4 C Ram ijkl 896

Above my example sql table how to delete row one by one.
Give me any one ideas.

What I have tried:

How to delete row one by one in sql server 2005.

Posted 21-Jul-16 6:29am

Boopalslm

Updated 21-Jul-16 8:19am

Add a Solution

Comments

Richard Deeming 21-Jul-16 13:26pm

If you're not using a primary key, or some other unique index, then how will you identify the row to delete? If you tell SQL to delete the row with ID = 1, then it will delete all rows with ID = 1.

You need to add a unique column to the table so that you can distinguish between the different duplicated rows.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2016-07-21T07:37:00

Solution 1

Something like this:

WHILE ((SELECT TOP (1) [Stuid] FROM [MyTable] GROUP BY [Stuid] HAVING Count(*)>1) > 0)
BEGIN
	DELETE TOP (1) FROM MyTable
	WHERE ((Stuid) In (SELECT [Stuid] FROM [MyTable] As Tmp GROUP BY [Stuid] HAVING Count(*)>1 ))
END

Posted 21-Jul-16 7:37am

RickZeeland

Updated 21-Jul-16 9:20am

v5

Comments

Richard Deeming 21-Jul-16 13:48pm

That will delete all copies of the duplicated row. I suspect the OP wants to leave one copy of each row behind.

RickZeeland 21-Jul-16 14:41pm

You are right ! I was a bit too hasty, I will update the solution.

Richard Deeming · Answer 2 · 2016-07-21T07:57:00

You're going to need to add a unique column to your table, so that you can distinguish between different copies of the same row:

SQL

ALTER TABLE YourTable
ADD TempRowNumber int IDENTITY(1, 1) NOT NULL;

Then you need to find and delete the duplicate rows. Assuming you only care about the Stuid column:

SQL

DELETE
FROM
    T1
FROM
    YourTable As T1
WHERE
    Exists
    (
        SELECT 1
        FROM YourTable As T2
        WHERE T2.Stuid = T1.Stuid
        And T2.TempRowNumber < T1.TempRowNumber
    )
;

If necessary, you can then drop the temporary unique column, and add a proper unique constraint:

SQL

ALTER TABLE YourTable
DROP COLUMN TempRowNumber;
GO
ALTER TABLE YourTable
WITH CHECK
ADD CONSTRAINT UX_YourTable_Stuid UNIQUE (Stuid);
GO

Peter Leow · Answer 3 · 2016-07-21T08:19:00

Solution 3

Check this out: https://support.microsoft.com/en-us/kb/139444[^]

Posted 21-Jul-16 8:19am

Peter Leow