April 23, 2003 at 12:17 pm
I have a new assignement. It is to code a program to remove exact duplicates of records in tables. I had done this before using a CURSOR and after my FETCH I would do an Update Where Current Cursor. I am now working with SQL Server 7.0, and Update or Delete are not supported. Ok so whats the replacement?? I cannot ad a unique record key field as each duplicate record set will all get the supposed "unique" key. If there is another way, please advise.
April 23, 2003 at 1:59 pm
You could select distinct * into newtable from existing_table and then rename the newtable.
You could select * into newtable from oldtable group by all_your_fields.
Couple of similar ideas.
Michelle
Michelle
April 24, 2003 at 3:09 am
I'm on SQL7 SP4 and can delete using current of cursor, e.g.
create table #t (a varchar(10))
insert into #t values ('A')
insert into #t values ('AA')
insert into #t values ('AA')
insert into #t values ('AAA')
insert into #t values ('AAA')
select * from #t
declare @a varchar(10),@x varchar(10)
set @x = ''
DECLARE curs CURSOR FOR select * from #t
OPEN curs
FETCH NEXT FROM curs INTO @a
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@a = @x)
BEGIN
DELETE FROM #t
WHERE CURRENT OF curs
END
ELSE
BEGIN
SET @x = @a
END
FETCH NEXT FROM curs INTO @a
END
CLOSE curs
DEALLOCATE curs
select * from #t
drop table #t
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2003 at 3:43 am
Another way is to do it is thorugh a self join matching every column to itself.
For example:
select a.*
from table a, table b
where a.col1 = b.col1
and a.col2 = b.col2
.....
and a.unique_id <> b.unique_id
and a.unique_id < b.unique_id /* Need this to prevent the duplicated row appearing twice */
If you are confident, you could wrap this type of query into a delete statement. Alternatively, put the rows into a temporary table, run some checks and then do the delete from the rows in the temporary table.
Also, I would wrap everything in begin transaction.... rollback transaction until you are 100% satisfied that you are deleting the right rows.
Jeremy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply