Corrupt tables? Update statement returning strange error

Question

Corrupt tables? Update statement returning strange error

Holly Kilpatrick

Ten Centuries

Points: 1195
More actions
December 15, 2009 at 6:13 am

#137645

With SQL 2005, a simple one-field update (either in query window update statement, or manually by opening a table and attempting to change a value) is failing with "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."
I understand that an update statement is executed by the system as a delete and an insert, and it appears that SQL Server is incorrectly creating the insert statement????? Has anyone seen this? Am I overlooking some setting? I have never run into this.
If I recreate the table, the problem is gone. But today another table in the same database has this issue, which was able to be updated yesterday. Is therre some way I can see exactly what insert statement SQL Server is trying to run?
Any ideas????
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Are there any triggers on the table?

If so, could be one of them that is causing the issue. Recreating the table makes the problem go away because the new table doesn't have any triggers.

EDIT. Hit Post before I meant to

Holly Kilpatrick Ten Centuries Points: 1195 More actions · Answer 2

Yes, it turns out there was a trigger, and the developer assured me that table had no trigger, and I could have sworn I checked for that, as I checked several others. Once I was "sure" there was no trigger on that table, I didn't look at that again, and kept looking for some unique mystery problem. But it was no mystery, just a case of carelessness on my part. Actually, that's good, SQL Server is still just as stable and reliable as I always thought it was.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Holly Kilpatrick (12/15/2009)
I understand that an update statement is executed by the system as a delete and an insert

Not always. SQL can do either in-place updates or a split update where it deletes the row then inserts it. Can't recall offhand the reasons why it would do the latter (though an update of the clustered index key is one of them). If you're curious, a google search should turn up details on this.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

GilaMonster (12/15/2009)
Can't recall offhand the reasons why it would do the latter

I'm sure I remember reading somewhere that any change to the Primary Key or a Unique Constraint causes the update to be done as a delete/insert.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Clustered index, yes. Primary key, Unique constraint, don't know. Maybe.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 6

Found the article:-

http://support.microsoft.com/kb/238254