blocks|key|215219|text|在这种情况下，可以通过将列添加为NOT+NULL并在一个语句as+per+my+answer+here中设置现有行的值来避免这个问题。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|215220|更广泛地说，这个问题是一个解析/编译问题。SQL+Server尝试在执行任何语句之前编译批处理中的所有语句。|215221|当一条语句引用了一个根本不存在的表时，该语句将受到延迟编译的影响。当表已经存在时，如果引用了不存在的列，则会抛出错误。解决这个问题的最好方法是在与DML不同的批处理中执行DDL。|215222|如果语句同时引用了现有表和不存在的表中不存在的列，则在延迟编译之前可能会抛出错误，也可能不会抛出错误。|215223|您可以在单独的批次中提交它(例如，使用客户端工具中的批次分隔符GO+)，或者在使用EXEC或EXEC+sp_executesql单独编译的子范围内执行它。|215224|第一种方法要求您重构代码，因为IF+...不能跨批处理。|215225|IF+NOT+EXISTS(SELECT+*
++++++++++++++FROM+++sys.columns
++++++++++++++WHERE++Name+=+'OPT_LOCK'
+++++++++++++++++++++AND+object_ID+=+Object_id('REP_DSGN_SEC_GRP_LNK'))
++BEGIN
++++++ALTER+TABLE+REP_DSGN_SEC_GRP_LNK
++++++++ADD+OPT_LOCK+NUMERIC(10,+0)

++++++EXEC('UPDATE+REP_DSGN_SEC_GRP_LNK+SET+OPT_LOCK+=+0');

++++++ALTER+TABLE+REP_DSGN_SEC_GRP_LNK
++++++++ALTER+COLUMN+OPT_LOCK+NUMERIC(10,+0)+NOT+NULL
++END;+|code-block|syntax|javascript|215226|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/12472606/73226^0|G|8|U|L|0|0|0|0|0|V|2|15|4|1A|I|0|F|6|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@$9|15|A|16|1|17]]|E|$]]|$1|F|3|G|5|6|7|18|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|19|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|1A|8|@]|D|@]|E|$]]|$1|L|3|M|5|6|7|1B|8|@$9|1C|A|1D|B|C]|$9|1E|A|1F|B|C]|$9|1G|A|1H|B|C]]|D|@]|E|$]]|$1|N|3|O|5|6|7|1I|8|@$9|1J|A|1K|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|R|7|1L|8|@]|D|@]|E|$S|T]]|$1|U|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

In this case you can avoid the problem by adding the column as <code>NOT NULL</code> and setting the values for existing rows in one statement <a href="https://stackoverflow.com/a/12472606/73226">as per my answer here</a>.

More generally the problem is a parse/compile issue. SQL Server tries to compile all statements in the batch before executing any of the statements.

When a statement references a table that doesn't exist at all the statement is subject to deferred compilation. When the table already exists it throws an error if you reference a non existing column. The best way round this is to do the DDL in a different batch from the DML. 

If a statement both references a non existing column in an existing table and a non existent table the error may or may not be thrown before compilation is deferred.

You can either submit it in separate batches (e.g. by using the batch separator <code>GO</code> in the client tools) or perform it in a child scope that is compiled separately by using <code>EXEC</code> or <code>EXEC sp_executesql</code>. 

The first approach would require you to refactor your code as an <code>IF ...</code> cannot span batches.

<pre><code>IF NOT EXISTS(SELECT *
 FROM sys.columns
 WHERE Name = 'OPT_LOCK'
 AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
 BEGIN
 ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ADD OPT_LOCK NUMERIC(10, 0)

 EXEC('UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0');

 ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
 END; 
</code></pre>

blocks|key|3069422|text|作为参考，您可以将IF+NOT+EXISTS替换为COL_LENGTH函数。它需要两个参数，|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3069423|要搜索的|3069424|表名和|ordered-list-item|3069425|列|3069426|3069427|如果找到该列，则返回列Ex:+Int+(4字节)的数据类型范围，如果未找到，则返回NULL。|3069428|因此，您可以按如下方式使用它，也可以将3个语句合并为一个语句。|3069429|IF+(SELECT+COL_LENGTH('REP_DSGN_SEC_GRP_LNK','OPT_LOCK'))+IS+NULL

BEGIN

++++ALTER+TABLE+REP_DSGN_SEC_GRP_LNK
++++ADD+OPT_LOCK+NUMERIC(10,+0)+NOT+NULL+DEFAULT+0

END;|code-block|syntax|javascript|3069430|让它变得更简单。|3069431|entityMap^0|9|D|P|A|0|0|0|0|0|15|4|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|16|8|@]|D|@]|E|$]]|$1|K|3|L|5|J|7|17|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|18|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|19|8|@$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1C|8|@]|D|@]|E|$]]|$1|R|3|S|5|T|7|1D|8|@]|D|@]|E|$U|V]]|$1|W|3|X|5|6|7|1E|8|@]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|Z|$]]

For your information,you can replace the <code>IF NOT EXISTS</code> with the <code>COL_LENGTH</code> function. It takes two parameters, 

<ol>
<li>Table Name and </li>
<li>Column you are searching for</li>
</ol>

If the Column is found then it returns the range of the datatype of the column Ex: Int (4 bytes), when not found then it returns a <code>NULL</code>.

So, you could use this as follows and also combine 3 Statements into one.

<pre><code>IF (SELECT COL_LENGTH('REP_DSGN_SEC_GRP_LNK','OPT_LOCK')) IS NULL

BEGIN

 ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ADD OPT_LOCK NUMERIC(10, 0) NOT NULL DEFAULT 0

END;
</code></pre>

Makes it simpler.

blocks|key|215232|text|该错误的根本原因是，在重新启动SQL+Server+Management+Studio之前，新添加的列名不会反映在sys.syscolumns和sys.columns表中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|215233|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The root cause of the error is the newly added column name is not reflected in the sys.syscolumns and sys.columns table until you restart SQL Server Management Studio.

blocks|key|210666|text|使用Go命令和默认值0来解决此问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|210667|--Add+all+columns,+if+they+don't+exist
ALTER+TABLE+REP_DSGN_SEC_GRP_LNK
++++ADD+OPT_LOCK+NUMERIC(10,+0)+NOT+NULL+DEFAULT+0
ALTER+TABLE+REP_DSGN_SEC_GRP_LNK
++++ALTER+COLUMN+OPT_LOCK+NUMERIC(10,+0)+NOT+NULL
GO
--Consume+new+columns
UPDATE+REP_DSGN_SEC_GRP_LNK+SET+OPT_LOCK+=+1+WHERE+SOMETHING+=+'ELSE'
GO|code-block|syntax|javascript|210668|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use the Go command and Default 0 to solve this.
<pre><code>--Add all columns, if they don't exist
ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ADD OPT_LOCK NUMERIC(10, 0) NOT NULL DEFAULT 0
ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
GO
--Consume new columns
UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 1 WHERE SOMETHING = 'ELSE'
GO
</code></pre>

Does anyone see what's wrong with this code for SQL Server?

<pre><code>IF NOT EXISTS(SELECT *
 FROM sys.columns
 WHERE Name = 'OPT_LOCK'
 AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
 BEGIN
 ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ADD OPT_LOCK NUMERIC(10, 0)

 UPDATE REP_DSGN_SEC_GRP_LNK
 SET OPT_LOCK = 0

 ALTER TABLE REP_DSGN_SEC_GRP_LNK
 ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
 END; 
</code></pre>

When I run this, I get:

<blockquote>
 Msg 207, Level 16, State 1, Line 3 
 Invalid column name 'OPT_LOCK'.
</blockquote>

on the update command.

Thanks.

Invalid column name on sql server update after column create

有没有人看到SQL Server的这段代码有什么问题？IF NOT EXISTS(SELECT *              FROM   sys.columns              WHERE  Name = 'OPT_LOCK'                     AND object_ID = Obje...

问创建列后sql server更新中的列名无效
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建列后sql server更新中的列名无效EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建列后sql server更新中的列名无效
EN