如何在SQL Server 2005中逐个删除行。 [英] How to delete row one by one in SQL server 2005.

问题描述

我正在使用数据库目的sql server 2005开发Windows应用程序，但在我的数据库中存储了一些重复记录。如何在sql server 2005中逐行删除行。这里我没有在主键中使用。

Ex：

Stuid stuname add1 add1 phone

1 A测试测试123

2 B abc def 456

1 A测试测试123

4 C Ram ijkl 896


在我的示例sql表之上如何逐个删除行。

给我任何一个想法。



我尝试了什么：



如何在sql server 2005中逐个删除行。

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.

推荐答案

这样的事情：

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  

您将需要添加一个唯一的列到你的桌子，这样你就可以区分同一行的不同副本：

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:

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

然后你需要找到并删除重复的行。假设您只关心 Stuid 列：

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

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:

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

检查出来：https://support.microsoft.com/en-us/kb/139444 [ ^ ]

这篇关于如何在SQL Server 2005中逐个删除行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！