Can't delete duplicate row

Hello,
I have a table in an SQL Server 2000 table that has no primary key, in fact
no indexes at all. (I didn't design this (:-)) )
I found that there is a duplicate row in the table but when I try to delete
it I get an error message:
"Key column information insufficient or incomplete. Too many rows were
affected by this update."
I had tried adding a primary key index but of course I couldn't since there
is a duplicate row.
I also tried just opening the table in EM and editing the data in the column
I want as primary key for one of the duplicate rows so I could set the
primary key, but I got an error message that the transaction can't start in
firehose mode.
Is there a simple way I can get out of this catch22 box? The DB supports a
busy Web site, so I was hoping to avoid shutting down the site.
Any help would be appreciated.
RagnarOne method:
SET ROWCOUNT 1
DELETE FROM PoorlyDesignedTable
WHERE KeyColumn = 'ValueWithDuplicate'
SET ROWCOUNT 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a table in an SQL Server 2000 table that has no primary key, in
> fact no indexes at all. (I didn't design this (:-)) )
> I found that there is a duplicate row in the table but when I try to
> delete it I get an error message:
> "Key column information insufficient or incomplete. Too many rows were
> affected by this update."
> I had tried adding a primary key index but of course I couldn't since
> there is a duplicate row.
> I also tried just opening the table in EM and editing the data in the
> column I want as primary key for one of the duplicate rows so I could set
> the primary key, but I got an error message that the transaction can't
> start in firehose mode.
> Is there a simple way I can get out of this catch22 box? The DB supports a
> busy Web site, so I was hoping to avoid shutting down the site.
> Any help would be appreciated.
> Ragnar
>|||Thank you Dan, that was really simple.
Ragnar
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uFeXRxjtFHA.3252@.TK2MSFTNGP10.phx.gbl...
> One method:
> SET ROWCOUNT 1
> DELETE FROM PoorlyDesignedTable
> WHERE KeyColumn = 'ValueWithDuplicate'
> SET ROWCOUNT 0
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ragnar Midtskogen" <ragnar_ng@.newsgroups.com> wrote in message
> news:e5kdNljtFHA.2064@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I have a table in an SQL Server 2000 table that has no primary key, in
>> fact no indexes at all. (I didn't design this (:-)) )
>> I found that there is a duplicate row in the table but when I try to
>> delete it I get an error message:
>> "Key column information insufficient or incomplete. Too many rows were
>> affected by this update."
>> I had tried adding a primary key index but of course I couldn't since
>> there is a duplicate row.
>> I also tried just opening the table in EM and editing the data in the
>> column I want as primary key for one of the duplicate rows so I could set
>> the primary key, but I got an error message that the transaction can't
>> start in firehose mode.
>> Is there a simple way I can get out of this catch22 box? The DB supports
>> a busy Web site, so I was hoping to avoid shutting down the site.
>> Any help would be appreciated.
>> Ragnar
>