Column or expression cannot be updated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Column or expression cannot be updated

Author

Topic

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-02 : 12:19:18

This is so weird! I have been using the following query for years in SQLServer 2000 and it always worked fine.

UPDATE T1
SET PostDatedCards = SumCards
FROM AllIndividuals AS T1 INNER JOIN
(SELECT AccountPaying, SUM(PostDatedCards) AS SumCards
FROM FinancialSQL.dbo.Transactions
GROUP BY AccountPaying) AS T2 ON T1.Account = T2.AccountPaying

Now, in SQL Server 2005 Management Studio, it no longer works. If I run the Query Analyser against it, it is properly veted with "The SQL syntax has been verified against the data source". But as soon as I try to run it, it indicates "Column or expression 'PostDatedCards' cannot be updated". BTW, upon running it, Management Studio alter the query to:

UPDATE T1
SET T1.PostDatedCards = T2.SumCards
FROM AllIndividuals AS T1 INNER JOIN
(SELECT AccountPaying, SUM(PostDatedCards) AS SumCards
FROM FinancialSQL.dbo.Transactions
GROUP BY AccountPaying) AS T2 ON T1.Account = T2.AccountPaying
CROSS JOIN T1

And if I try to run that system generated syntax, I get "Invalid object name T1" with the error source being ".Net SQLClient Data Provider".

What the heck is going on with the data provider?

Thanks!

Mike

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-02 : 13:05:49

Looks like the second version correctly included the table aliases in the SET clause. But I don't understand why you have the CROSS JOIN in there. The error is because you can't JOIN to a table alias, you would need to JOIN to the actual table name and then give it a new alias (as another instance of that table).

Be One with the Optimizer
TG

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-02 : 13:57:36

Thanks TG, that worked.

Does anyone know of a source that would list the T-SQL changes between 2000, 2005 and 2008 depending on the provider used? My problem is that most of what we do is in VB code using the SQLOLEDB provider where the 1st query above works fine, whether applied against SQL Server 2000 or SQL Server 2005. For consistency, I suppose that we should now use a different provider based on the native client... otherwise we'll keep on getting surprises like the above when using the Management Studio. Then again, maybe I'm totally in left field. Any thoughts anyone?

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-02 : 14:11:34

Actually, let me correct that last post: TG's suggestion does not work. The syntax is accepted but the query does not produce the expected results. I ran the original query in VB code against SQL Server 2005 and it runs correctly.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-02 : 14:31:16

Well you didn't post any details on why it's incorrect or an answer to why there a "cross join"?
The original statement doesn't use the CROSS JOIN but the updated statement does. Is that the difference?

Post the actual statement that you are using that is producing incorrect results so we can compare it to your original working statement.

Be One with the Optimizer
TG

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-02 : 15:22:57

TG, the statement which is producing the incorrect results is:

UPDATE AllIndividuals
SET T1.PostDatedCards = T2.SumCards
FROM AllIndividuals AS T1 INNER JOIN
(SELECT AccountPaying, SUM(PostDatedCards) AS SumCards
FROM FinancialSQL.dbo.Transactions
GROUP BY AccountPaying) AS T2 ON T1.Account = T2.AccountPaying
CROSS JOIN T1

The result returned is that the PostDatedCards value is set the same for all accounts, whereas this value should only apply to the first account. However, when I run the following through VB code (but only through code because Management Studio will not accept it), I get the correct result for each and every account:

UPDATE T1
SET PostDatedCards = SumCards
FROM AllIndividuals AS T1 INNER JOIN
(SELECT AccountPaying, SUM(PostDatedCards) AS SumCards
FROM FinancialSQL.dbo.Transactions
GROUP BY AccountPaying) AS T2 ON T1.Account = T2.AccountPaying

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 15:34:33

What happens when you run this in uery Analyser?

UPDATE T1
SET T1.PostDatedCards = T2.SumCards
FROM AllIndividuals AS T1 INNER JOIN
(SELECT AccountPaying, SUM(PostDatedCards) AS SumCards
FROM FinancialSQL.dbo.Transactions
GROUP BY AccountPaying) AS T2 ON T1.Account = T2.AccountPaying

Madhivanan

Failing to plan is Planning to fail

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-02 : 16:04:16

As per the original post, I get "The SQL syntax has been verified against the data source". But as soon as I try to run it, it indicates "Column or expression 'PostDatedCards' cannot be updated".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:52:02

Is PostDatedCards a computed column or an identity column or a guid column? You cant update these types of columns.

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-03 : 08:36:10

Computed column:FALSE, Identity column: FALSE, Key: FALSE, just a simple REAL data type column. Both databases are at compatibility level 90. I'm logging into SQL Server 2005 Management Studio with the same login as I do through VB code. The login has owner rights to both DBs.

But I don't believe that the problem is at column, table or even DB level. The problem seems to be with SQL Server 2005 Management Studio. You see, for the sake of clarity, the original query
- ran fine in the Enterprise Manager of SQL Server 2000
- still currently runs fine when performed from VB code against SQL Server 2005 using the usual login credentials
- will not run in SQL Server 2005 Management Studio even though the Query Analyser in Management Studio says
"The SQL syntax has been verified against the data source".

Further information: the exact error message, this time including the dialog context, is: "The following syntax errors were encountered while parsing the contents of the SQL pane: Column or expression 'PostDatedCards' cannot be updated."

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-03 : 13:02:22

More info: I just executed the original query in VB code using the SQLNCLI provider instead of SQLOLEDB and the query ran perfectly. This establishes that my problem has absolutely nothing to do with the query syntax. The problem seems to reside entirely within SQL Server 2005 Management Studio interface. This makes me very queazy. I have made no option changes in Management Studio, everything is set to factory defaults. This is too weird!!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-03 : 13:48:40

quote:
Originally posted by WindChaser

More info: I just executed the original query in VB code using the SQLNCLI provider instead of SQLOLEDB and the query ran perfectly. This establishes that my problem has absolutely nothing to do with the query syntax. The problem seems to reside entirely within SQL Server 2005 Management Studio interface. This makes me very queazy. I have made no option changes in Management Studio, everything is set to factory defaults. This is too weird!!!!

I doubt you run in it in Query Analyser

In Management studio, click on the New query in the tool bar and execute there

Madhivanan

Failing to plan is Planning to fail

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-03 : 14:31:02

madhivanan,

As per your suggestion, I clicked on New Query and ran the query: it runs perfectly. I feel better already!

But then, can you tell me what I'm doing wrong in the SQL Server 2005 Management Studio interface:
1. From the Object Explorer, I expand the database containing the AllIndividuals table
2. expand the tables folder
3. right-click on the dbo.AllIndividuals table
4. select Open table
5. in the query analyzer bar, I then click on the Show SQL Pane icon
6. paste the query in the pane
7. click on the Verify SQL syntax icon for a preliminary verification
8. click on the Execute SQL icon

These benign steps result in error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-03 : 17:17:38

quote:
Originally posted by WindChaser

madhivanan,

As per your suggestion, I clicked on New Query and ran the query: it runs perfectly. I feel better already!

But then, can you tell me what I'm doing wrong in the SQL Server 2005 Management Studio interface:
1. From the Object Explorer, I expand the database containing the AllIndividuals table
2. expand the tables folder
3. right-click on the dbo.AllIndividuals table
4. select Open table
5. in the query analyzer bar, I then click on the Show SQL Pane icon
6. paste the query in the pane
7. click on the Verify SQL syntax icon for a preliminary verification
8. click on the Execute SQL icon

These benign steps result in error.

I suggest you to depend on Query Analyser to execute
The way you did is not always reliable

Madhivanan

Failing to plan is Planning to fail

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-03 : 21:54:29

It certainly seems so. But I can't believe this is intentional on the part of Microsoft. Using the pane has been a standard way to proceed since SQL Server 7. Why would the user be allowed to perform a query using a unreliable methodology?!?! It just doesn't add up.

Would anyone have any insight to enlighten me?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-04 : 00:40:38

I think that "open table" is just there as a convenience to get a quick look at the table content by people who don't necessarily know SQL. The "show sql" is just so you can slap a "top 10" or something in there. I don't believe it is intended to perform DML operations. I'm not surprised it doesn't allow you to UPDATE data.

Be One with the Optimizer
TG

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-04 : 08:10:07

TG, but you see, you can update... just not always reliably. This is not a game program, this is SQL Server, a mission-critical application. Therefore, intrinsically, if the interface allows you to perform an operation, no matter whether it's a simple update, a complex update, or anything else, you should be able to depend on the operation being performed accurately, or else the interface should block the operation entirely. This is why I cannot believe that this behavior is intentional on the part of Microsoft. Do you really believe that Microsoft might say "let's allow the users to attempt it, even though we know that there's a risk that the results/effects will not be accurate, and let's all go home for the week-end."?

contrari4n
Starting Member

27 Posts

Posted - 2008-07-04 : 17:00:04

Here's your answer:

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

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

WindChaser
Posting Yak Master

225 Posts

Posted - 2008-07-04 : 19:44:49

Thanks Richard. My world makes sense again! Looking forward to SP3 and/or SQL Server 2008.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources