SQL更改表，然后修改值 [英] SQL Alter Table then Modify Values

问题描述

我有一个正在使用的SQL脚本，在创建（或编辑）列然后尝试修改该新列时遇到问题。

例如：

  BEGIN 
 ALTER TABLE SampleTable添加列Three int 
 END 
 
 IF（存在（选择*从sys.columns，其中WHERE名称='ColumnThree'））
开始
更新SampleTable SET ColumnThree = 0 
 END 
  
 
 
做这样的事情的正确方法是什么？（我似乎有几种类似的情况需要这样做）。
解决方案
您需要  GO  ，而不是 BEGIN / END 。此外，您可能需要稍微修改一下 EXISTS 查询为确保您获得正确的表：
  ALTER TABLE SampleTable添加列Three int 
 GO 
 
 IF（存在
（从
 SELECT 1 
 sys.columns c 
 INNER JOIN sys.tables t ON 
 c.object_id = t.object_id 
 WHERE 
 t.name ='SampleTable'
 AND c.name ='ColumnThree'））
开始
更新SampleTable SET ColumnThree = 0 
 END 
  
如果您使用多个模式，您也希望通过 sys.schemas 进入检查。
 
I have a SQL script that I am working on and I run into an issue when I'm creating (or editing) a column and then attempting to modify that new column.

For example:
BEGIN
    ALTER TABLE SampleTable ADD ColumnThree int 
END

IF (EXISTS (SELECT * FROM sys.columns WHERE name = 'ColumnThree'))
BEGIN
    UPDATE SampleTable SET ColumnThree = 0
END
Now I thought the BEGIN/END blocks would separate those two items out, but I get an error "Invalid column name 'ColumnThree'." when I attempt to run this.  Why?  Shouldn't the first BEGIN/END set up that ColumnThree and more to the point the IF(EXISTS should protect the UPDATE statement from being run if that column name doesn't exist.

What is the correct way to do something like this?  (I seem to have a couple of similar scenarios where this is needed).
解决方案
You need GO, not BEGIN/END. Also, you may want to edit your EXISTS query a bit to ensure you're getting the right table:
ALTER TABLE SampleTable ADD ColumnThree int 
GO

IF (EXISTS 
        (SELECT 1 
         FROM 
             sys.columns c 
             INNER JOIN sys.tables t ON 
                 c.object_id = t.object_id 
         WHERE 
             t.name = 'SampleTable' 
             AND c.name = 'ColumnThree'))
BEGIN
    UPDATE SampleTable SET ColumnThree = 0
END
If you're using multiple schemas, you will want to through sys.schemas into the check, as well.

                        
这篇关于SQL更改表，然后修改值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！