sp_rename 'FORUM_A_REPLY', 'TEMP_FORUM_A_REPLY'

select distinct *
into FORUM_A_REPLY
from TEMP_FORUM_A_REPLY

drop table TEMP_FORUM_A_REPLY

DECLARE @Count int
DECLARE @REPLY_ID nvarchar(50)


DECLARE duplicate_cursor CURSOR FAST_FORWARD FOR 
SELECT REPLY_ID, Count(*) - 1
FROM FORUM_A_REPLY 
GROUP BY REPLY_ID 
HAVING Count(*) > 1

OPEN duplicate_cursor 

FETCH NEXT FROM duplicate_cursor INTO @REPLY_ID, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM FORUM_A_REPLY WHERE REPLY_ID = @REPLY_ID
SET ROWCOUNT 0

FETCH NEXT FROM duplicate_cursor INTO @REPLY_ID, @Count
END

CLOSE duplicate_cursor 
DEALLOCATE duplicate_cursor

quote:

---

How can I check I have the correct amount of reply's after it executes ?

---

DECLARE @prev int;
DECLARE @ID int;
DECLARE RC CURSOR 
DYNAMIC
FOR SELECT REPLY_ID FROM FORUM_A_REPLY ORDER BY REPLY_ID;

SET @prev=0
OPEN RC

FETCH NEXT FROM RC INTO @ID

WHILE @@FETCH_STATUS=0
BEGIN

   IF @ID = @prev
      UPDATE FORUM_A_REPLY SET R2=1 WHERE CURRENT OF RC
   Else
      BEGIN
	UPDATE FORUM_A_REPLY SET R2=0 WHERE CURRENT OF RC
        SET @prev = @ID
      END

 FETCH NEXT FROM RC INTO @ID

END

CLOSE RC
DEALLOCATE RC

DELETE FROM FORUM_A_REPLY WHERE R2=1

quote:

---

I'm almost sure it will work with damaging anything of value.

---

quote:

---

Originally posted by Podge

quote:

---

I'm almost sure it will work with damaging anything of value.

---

I hope thats a typo

I'm going to backup the database and try both to see if they return the same results.

---

quote:

---

The problem is, even for duplicate replies, you want to keep one of those, right?

---

SET ROWCOUNT @Count -- this count will always be one less than the amount of duplicates
DELETE FROM FORUM_A_REPLY WHERE REPLY_ID = @REPLY_ID
SET ROWCOUNT 0

DECLARE @prev int;
DECLARE @ID int,@R3 int;
DECLARE RC CURSOR 
FOR SELECT REPLY_ID,R3 FROM FORUM_A_REPLY ORDER BY REPLY_ID;

SET @prev=0
OPEN RC

FETCH NEXT FROM RC INTO @ID,@R3

WHILE @@FETCH_STATUS=0
BEGIN

   IF @ID = @prev
      UPDATE FORUM_A_REPLY SET R2=1 WHERE R3=@R3
   Else
      BEGIN
	UPDATE FORUM_A_REPLY SET R2=0 WHERE R3=@R3
        SET @prev = @ID
      END

 FETCH NEXT FROM RC INTO @ID,@R3

END

CLOSE RC
DEALLOCATE RC

DELETE FROM FORUM_A_REPLY WHERE R2=1

Alter table FORUM_A_REPLY ADD R2 int NULL
ALTER TABLE FORUM_A_REPLY ADD R3 int IDENTITY(1,1)