Please translate this message for me 2

[link9]

Insufficient or incorrect key column information; too many rows affected by update

Comes up in a little annoying pop up box -- always seems to be attached to one row in particular, and it just won't let me change anything on that row.

The data I'm trying to update is just fine -- trying to go from a 3 to a 4, and I can do it on other rows with no problem whatsoever.

This little error message has been a thorn in my side for months. I usually just deal with it because I just can't make any sense of it, but I just about destroyed my box a second ago because of it, and it would be helpful to my career if I didn't do that -- Please help!!!

thanks
Paul Prewett

 

[Cagliostro]

It is yeasy,
Tha first possible case is:
------
update x set
y=(select z from w)
------
if the query 'select z from w' returns many than one row, means what SQL Server doesn't thow wich one to put in y to update. In this case you must change your subquery to return only one value. Other possible case is
------
update x set
y=(select z1, z2 from w)
------
there is an error, I think SQL Server will not translate this query.
John Fill

 

[link9]

I see your point, but I'm talking working directly with the table. I've got the table open in Enterprise Manager, and I click on a field, and try to change the value, and it just flat out won't let me...

any more ideas?

thx
paul

 

[jballum]

Paul,

Still the same answer as what John Fill told you above.

The table is open in Enterprise Manager and you change a field. When you tab out of the record, in the background Enterprise Manager builds and then performs an update statement.

Does this table have a primary key or unique index? I would guess that it doesn't and you are trying to update a record that has an exact duplicate.

JB

 

[link9]

Ahhh -- ok, then. **smacks head**

Thanks, guys

 

[Cagliostro]

If in a table are two identical rows, SQLServer do not recognize wich to change:
OneTable:
column1 column2 column3
a b c
a b c
a b c
in this case you should add an increment row
ID column1 column2 column3
1 a b c
2 a b c
3 a b c
oon changing the last, SQL Server will have enough information to know what it is a different row.
John Fill