| Author |
 Topic  |
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
 Posted - 27 November 2003 : 08:24:40
|
Whilst archiving a forum my browser hung, I shut it down and tried again and it went ok. Thought nothing of it until I search the archive and found that each thread had 2 replied the same! My first attempt must have copied them across and crashed before deleting them from the FORUM_REPLY table therefore been copied again on my second attempt.
I can't figure out how I can clean this up without going though the db one by one! I have EM so can run SQL script if anyone can lend a hand? |
The UK MkIVs Forum |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 09:18:26
|
if the table doesn't have a unique id field, then add one and do
SELECT A1.*
from FORUM_A_REPLY A1, FORUM_A_REPLY A2
where A1.REPLY_ID = A2.REPLY_ID
AND A1.UID <> A2.UID
|
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 09:27:00
|
Ok, this should help a bit more
Alter table FORUM_A_REPLY add NewPK int NULL
go
-- populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update FORUM_A_REPLY
SET @intCounter = NewPK = @intCounter + 1
-- ID the records to delete and get one primary key value also
-- We'll delete all but this primary key
select REPLY_ID, RecCount=count(*), PktoKeep = max(NewPK)
into #dupes
from FORUM_A_REPLY
group by REPLY_ID
having count(*) > 1
order by count(*) desc, REPLY_ID
-- delete dupes except one Primary key for each dup record
delete *
from FORUM_A_REPLY a join #dupes d
on d.reply_id = a.reply_id
where a.NewPK not in (select PKtoKeep from #dupes)
-- remove the NewPK column
ALTER TABLE FORUM_A_REPLY DROP COLUMN NewPK
go
drop table #dupes
|
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 27 November 2003 : 09:40:24
|
cheers
i get an incorrect syntax error near * line 18
and #dupe doesn't exist |
The UK MkIVs Forum |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 10:05:37
|
change this line
delete *
to just
delete FORUM_A_REPLY
do you mean #dupe or #dupes ?? |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
|
|
Jeepaholic
Average Member
USA
697 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 12:36:29
|
quote:
Originally posted by DavidRhodes
sorry, meant #dupes
You shouldn't be getting any errors for that, it is a temp table. |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 27 November 2003 : 13:34:28
|
#dupes is not erroring now.
I have got up to the delete statement, eg #dupes is populated, but i'm getting an error on -- delete dupes except one Primary key for each dup record
delete
from FORUM_A_REPLY a join #dupes d
on d.reply_id = a.reply_id
where a.NewPK not in (select PKtoKeep from #dupes)
You can't delete on joins can you? The error is before the word join |
The UK MkIVs Forum |
Edited by - DavidRhodes on 27 November 2003 13:55:05 |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 27 November 2003 : 14:07:41
|
I'm not sure that delete statement would be correct?
I have ran the query that Huw posted first and all the duplicates have the same value for NewPK, eg looking down the query results it displays 1,1,2,2,3,3,4,4,5,5,6,6 and so on.
Non of the values in #dupes.PKtoKeep match any values in FORUM_A_REPLY.NewPK so the statement "where a.NewPK not in (select PKtoKeep from #dupes)" would be pointless? |
The UK MkIVs Forum |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 14:10:28
|
quote:
Originally posted by HuwR
change this line
delete *
to just
delete FORUM_A_REPLY
do you mean #dupe or #dupes ??
did you change the
delete * to delete FORUM_A_REPLY ?
the logic for the code is correct, #dupes has the newkey for the ones you are keeping, that is why you are deleting the one not in #dupes |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 27 November 2003 : 14:15:50
|
I trieddelete from FORUM_A_REPLY a join #dupes d
on d.reply_id = a.reply_id
where a.NewPK not in (select PKtoKeep from #dupes)
Don't you still need the "FROM"?
I tried without the FROM and got the same error |
The UK MkIVs Forum |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 14:36:28
|
| no, just change the delete * line to delete FORUM_A_REPLY, do not change anything else. |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 27 November 2003 : 14:42:09
|
that worked, thank you 
I never new you could do DELETE <table_name> FROM......... |
The UK MkIVs Forum |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 27 November 2003 : 14:44:30
|
quote:
Originally posted by HuwR
if the table doesn't have a unique id field, then add one and do
SELECT A1.*
from FORUM_A_REPLY A1, FORUM_A_REPLY A2
where A1.REPLY_ID = A2.REPLY_ID
AND A1.UID <> A2.UID
UID doesn't exist in FORUM_A_REPLY? |
The UK MkIVs Forum |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 27 November 2003 : 14:51:18
|
the second post superceded the first, just ignore that post.
UID would have refered to "if the table doesn't have a unique id field, then add one" |
|
|
| |
Topic |
|
Topic Locked
