Any suggestions please?

Question

Post reply

Any suggestions please?

Thomas Cavanaugh

Grasshopper

Points: 19
More actions
June 14, 2005 at 7:57 am

#87977

I have a table in SQL Server 7. This table has no key field defined. I have always been able to delete records from this table before. Now when I try to delete a record I get this message: "Key column information is insufficient or incorrect. Too many rows were affected by update." There is no key column and deleting this record shouldn't orphan any records. Any ideas?

Thanks
Tom Cavanauigh

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Maybe there was a unique index that's been deleted.

Or did you change the context of the delete (likw now using access when the old app was in vb)?

Thomas Cavanaugh Grasshopper Points: 19 More actions · Answer 2

There never should have been any unique indexes or unique keys for this table. This table is the many in a one to many relationship. We have always used VB for the application associated with this DB. How could I tell if someone used Access instead?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You'd have to run the profiler.

The fact is that something somewhere in the application or server changed (or was flawed from design and just now is breaking). You have to find what's changed.

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 4

By what method are you trying to perform the deletion? From within Enterprise Manager...?

If you're trying to delete a duplicate row from within EM then you will always get this error.

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Have you tried running an sp_depends against the table to see if anything's changed ?!

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Ninja's_RGR'us

SSC Guru

Points: 294069

June 14, 2005 at 9:29 am

#565376

Checking for missing foreign keys??

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 7

Sounds more to me like the rows deleted in the past have not been duplicates, and this one is, rather than being that anything has changed.

This is an Enterprise Manager (or provider?) error not a SQL Server one because you've chosen to delete one of a duplicate row. You wouldn't get this from a SQL statement. Because they are identical it cannot differentiate between the two and thus cannot delete just one of the rows - it has to delete all or nothing.

Check this out (second question) or Google on the message.

You can also get the error using a Recordset object in ADO - similar scenario. I've never seen another reason for this error, though I'm open to surprises.

JP de Jong-202059 SSCommitted Points: 1674 More actions · Answer 8

Every table needs a unique key! In my experience, I always got this message when a developer forgot to implement unique indexes on a table.

I suggest start reading articles on database design and normalization. (1NF, 2NF and 3NF...)

JP

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 9

I agree with paul. This error will come when we try delete duplicate rows with enterprise manager. Entire row is a duplicate. Since there is no key specified for this table the duplicate rows can be deleted in Query Analyser.

(Unless you worked with a bad database design you woul never have faced this problem. Many experts in this forum never have faced this problem since they should have designed a table always with a unique key. Which is a rule to create a table)

SELECT DISTINCT * FROM TableName INTO #MyRow FROM TableName WHERE column1 = value and Column2 = value Column3 = value (Should mention for all columns available since no primary, unique key or timestamp is in the table)

delete from TableName where column1 = value and Column2 = value Column3 = value(Should mention for all columns available since no primary, unique key or timestamp is in the table)

INSERT TableName SELECT * FROM #MyRow --GET your one row back in the table

Regards,
gova

Thomas Cavanaugh Grasshopper Points: 19 More actions · Answer 10

The rows I was trying to delete from Enterprise Manager were duplicates. After reading some of the posts, I deleted all of the duplicates and the orginal using query analyzer. I then just re entered the original. Thank you all for the help!

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 11

Would this table not benefit from a primary key? As already said, it should have one anyway, and if you're trying to avoid duplicates then that speaks for itself.

SueB SSCommitted Points: 1668 More actions · Answer 12

When just needing a quick fix I have created a identity field, deleted the duplicate record and then deleted the identity field.

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 13

Hi,

If the problem is still existing ,

do the following.

As you might have got that its coming because of duplicate records. so you need to correct the same by removing the duplicate records from this table.

try this,

select fld1,fld2,fldn from table group by fld1,fld2,fldn having count(*) > 1

this will return all the duplcate records

now run a loop against this records for delete. before use thsi command

set rowcount 1

delete the records where fld1 = @value and fld2 = @value

set RowCount 0

this rowcount stuff will make sure that you are deleting only one record.

run the looptill the query dont return any rows.

select fld1,fld2,fldn from table group by fld1,fld2,fldn having count(*) > 1

pls let me know if this solves teh problem ort you want the same in detail.

Jeswanth

--------------------------------