November 5, 2003 at 2:21 pm
I am trying to delete duplicates from one of my tables
I did the following two steps.
SELECT Col1, col3=count(*)
INTO holdkey
FROM t1
GROUP BY Col1
HAVING count(*) > 1
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE dbo.local_tier_1_ibm_tickets.Col1 = holdkey.Col1
But in second step i got the error
Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.
Is there any way to do this.
Thanks.
November 6, 2003 at 4:18 am
You can name tthe columns and use convert(varchar(7000,txtcol) on the text column.
Do you want to include the text in the distinct?
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
November 10, 2003 at 2:48 pm
You can run this in a job if you want. It will go through a table and delete all duplicate names. If you need to delete something else jsut change what you are looking for
Here's a sample table
CREATE TABLE [dbo].[Table2] (
[TID] [int] IDENTITY (1, 1) NOT NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] WITH NOCHECK ADD
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[TID]
) ON [PRIMARY]
GO
******************************************
Code to delete duplicate names out of the sample table
******************************************
CREATE PROCEDURE Delete_FName
AS
CREATE TABLE #TempName (PK INT IDENTITY NOT NULL PRIMARY KEY, FName VARCHAR(50), FNCount INT)
INSERT INTO #TempName
(FName,
FNCount)
SELECT
FName,COUNT(FName) AS FNCount FROM table2 GROUP BY FName HAVING COUNT(FName) >1
DECLARE @CheckCount INT
DECLARE @RecordCount INT
DECLARE @DeleteName INT
DECLARE @I INT
DECLARE @FName VARCHAR(50)
SET @RecordCount = (SELECT COUNT(FName) FROM #TempName)
SET @I = 1
WHILE @I <= @RecordCount
BEGIN
SET @FName = (SELECT FName FROM #TempName WHERE PK = @I)
SET @DeleteName = (SELECT TOP 1 TID FROM Table2 WHERE FName = @Fname)
SET @CheckCount = (SELECT COUNT(FName) FROM Table2 GROUP BY FName HAVING FName = @FNAME )
PRINT @RECORDCount
IF @CheckCount > 1
BEGIN
DELETE
Table2
WHERE TID = @DeleteName
SET @CheckCount = (SELECT COUNT(FName) FROM Table2 GROUP BY FName HAVING FName = @FNAME )
Continue
END
ELSE
BEGIN
EXEC Delete_FName
BREAK
END
IF @CheckCount > 1 or @I = @RecordCount
BEGIN
SET @I = @I +1
Continue
END
ELSE
BEGIN
SET @I = @I
Continue
END
Continue
END
GO
Edward M. Sokolove
Edward M. Sokolove
November 10, 2003 at 4:58 pm
Read today's headline on SQLServerCentral.com
http://www.sqlservercentral.com/columnists/darjun/deletingduplicaterows.asp
Linto
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply