unable to delete duplicate records in database

[Barbara]

Hi,
I have an sql database that has the primary key set to three fields,
but has not been set as unique(I didn't create the table).
I have 1 record that has 2 duplicates and I am unable to delete the
duplicate entries.
If I try to delete any of the three records(they are identical) I get
the message 'key column is insufficient or incorrect. Too many rows
were affected by update'.
I am trying to do this within Enterprise Mgr.
Any suggestion?
Thanks much

[lucjan]

Re: unable to delete duplicate records in database

Hi,
go to Query Analyzer, and

set rowcount 1

delete from mytable .. where key1=..., key2=.. , key3=..

this will get rid of one row only.


To fix the whole table, you could do the following:

sp_rename mytable, oldtable
go
create mytable(... copy of mytable.. )
....
go

insert into mytable(...)
select DISTINCT.. all the columns from oldtable
go

drop table oldtable
go


hope this helps,

Lucjan



"Barbara" <barbara.mccorm ick@cph-inc.com> wrote in message
news:c613a946.0 401160927.55e79 210@posting.goo gle.com...[color=blue]
> Hi,
> I have an sql database that has the primary key set to three fields,
> but has not been set as unique(I didn't create the table).
> I have 1 record that has 2 duplicates and I am unable to delete the
> duplicate entries.
> If I try to delete any of the three records(they are identical) I get
> the message 'key column is insufficient or incorrect. Too many rows
> were affected by update'.
> I am trying to do this within Enterprise Mgr.
> Any suggestion?
> Thanks much[/color]

[Simon Hayes]

Re: unable to delete duplicate records in database


"Barbara" <barbara.mccorm ick@cph-inc.com> wrote in message
news:c613a946.0 401160927.55e79 210@posting.goo gle.com...[color=blue]
> Hi,
> I have an sql database that has the primary key set to three fields,
> but has not been set as unique(I didn't create the table).
> I have 1 record that has 2 duplicates and I am unable to delete the
> duplicate entries.
> If I try to delete any of the three records(they are identical) I get
> the message 'key column is insufficient or incorrect. Too many rows
> were affected by update'.
> I am trying to do this within Enterprise Mgr.
> Any suggestion?
> Thanks much[/color]

Some front end tools (possibly including Enterprise Manager) cannot modify
data in a table without a primary key, because they cannot know which row(s)
they are affecting. You should be able to use Query Analyzer to delete two
of the rows:

set rowcount 2

delete MyTable
where pkcol1 = 1 and pkcol2 = 2 and pkcol3 = 3

set rowcount 0

Simon