菜鸟错误

菜鸟错误 - 列或表达式无法更新(Rookie error - column or expression cannot be updated)

感谢您抽出宝贵时间阅读这篇SQL新秀的姗姗来迟的请求; 非常感谢。

我正在尝试使用我认为已经使用了数百次的代码在Server Management Studio 2012中运行更新语句，但由于某种原因，它会向我抛出错误。 所以也许我没有。

我一直在看这个，删除了所有我原来的别名，尝试重写它并尝试不同的连接。 我已经搜索过类似的错误，但大多数似乎都指向使用错误的别名或错误使用WHERE子句。 我承认，在这里可能就是这种情况，但没有一个与我的情况类似。

在语句上使用SELECT给出了我期待的结果，代码通过了语法检查，但我收到了这条消息。

列或表达式'DIS_ID'无法更新

当我尝试运行它。 我认为这是一件很简单的事，我做错了，因为我对此很新，但我已经筋疲力尽了我想到的一切。 我承认不是不同类型连接的专家，但我使用LEFT或RIGHT连接获得相同的SELECT结果。

UPDATE DIS_Territories SET DIS_Territories.DIS_ID = '1' FROM DIS_Territories INNER JOIN Territories ON DIS_Territories.BT_ID = Territories.BT_ID WHERE (BT_State = 'Louisiana') AND (BT_County = 'Bienville') OR (BT_State = 'Louisiana') AND (BT_County = 'Bossier') OR (BT_State = 'Louisiana') AND (BT_County = 'Richland') OR (BT_State = 'Louisiana') AND (BT_County = 'Union') OR (BT_State = 'Louisiana') AND (BT_County = 'Webster') OR (BT_State = 'Louisiana') AND (BT_County = 'West Carroll')

我试图修改的列的数据类型是Integer，不允许空值。 如果我遗漏了任何重要信息，我很抱歉，如果有人让我知道我应该提供什么，我将非常感激。

编辑： DIS_Territories表结构如下：

USE [live.data] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Dis_Territories]( [BDT_ID] [int] IDENTITY(1,1) NOT NULL, [BT_ID] [int] NULL, [DIS_ID] [int] NULL ) ON [PRIMARY] GO

为了记录，我在开发和实时数据库上遇到了同样的问题。

非常感谢，如果你还在读书。

-Rodger

Thanks for taking the time to read this SQL rookie's belated plea; it's much appreciated.

I'm trying to run an update statement in Server Management Studio 2012 using code that I thought I'd used hundreds of times, but for some reason, it's throwing an error back at me. So maybe I haven't.

I've been looking at this one for a while, removed all my original aliases, tried rewriting it and experimenting with different joins. I've searched all over for similar errors, but most seem to point to using the wrong aliases or incorrect use of the WHERE clause. Either might be the case here, I admit, but none is similar to the situation I have.

Using SELECT on the statement gives me the results I was expecting and the code passed the syntax checks, but I'm getting this message.

column or expression 'DIS_ID' cannot be updated

When I try to run it. I assume it's something simple that I'm doing wrong, as I'm fairly new at this, but I've exhausted everything I can think of trying. I will admit to not being an expert in different types of joins, but I get the same SELECT results using a LEFT or RIGHT join.

UPDATE DIS_Territories SET DIS_Territories.DIS_ID = '1' FROM DIS_Territories INNER JOIN Territories ON DIS_Territories.BT_ID = Territories.BT_ID WHERE (BT_State = 'Louisiana') AND (BT_County = 'Bienville') OR (BT_State = 'Louisiana') AND (BT_County = 'Bossier') OR (BT_State = 'Louisiana') AND (BT_County = 'Richland') OR (BT_State = 'Louisiana') AND (BT_County = 'Union') OR (BT_State = 'Louisiana') AND (BT_County = 'Webster') OR (BT_State = 'Louisiana') AND (BT_County = 'West Carroll')

The data type for the column I'm trying to amend is Integer, not allowing nulls. If I'm missing any crucial information, my apologies and I'd be very grateful if someone would let me know what else I should provide.

EDIT: The DIS_Territories table structure is as follows:

USE [live.data] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Dis_Territories]( [BDT_ID] [int] IDENTITY(1,1) NOT NULL, [BT_ID] [int] NULL, [DIS_ID] [int] NULL ) ON [PRIMARY] GO

For the record, I'm having the same issue on both my development and live databases.

Thanks very much if you're still reading.

-Rodger

最满意答案

我在SQL Fiddle中使用我想象中的数据测试了你的SQL，它运行得很好。

我和那些留下评论的人有同样的怀疑，表中有没有你没注意到的东西？ 是列标识还是计算列？ DIS_Territories是一个视图吗？

OK, thanks very much to everyone who took a look.

I've done most of my editing in the edit rows window, which is where I was encountering the error. I ran it in the new query window instead, and it went off without a hitch. Not 100% sure if the fault is on my end of Server Management Studio, but it seems to work for now. Fingers crossed.