SQL 更改表然后修改值

发布于 2024-08-02 03:21:22 字数 473 浏览 7 评论 0原文

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

例如：

BEGIN
    ALTER TABLE SampleTable ADD ColumnThree int 
END

IF (EXISTS (SELECT * FROM sys.columns WHERE name = 'ColumnThree'))
BEGIN
    UPDATE SampleTable SET ColumnThree = 0
END

现在我认为 BEGIN/END 块会将这两个项目分开，但我收到错误“无效的列名 'ColumnThree'”。当我尝试运行这个时。为什么？第一个 BEGIN/END 不应该设置 ColumnThree 及更多内容，如果该列名不存在，IF(EXISTS 应该保护 UPDATE 语句不被运行。

执行此类操作的正确方法是什么？(我似乎有几个类似的场景需要这样做）。

原文

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).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

记忆里有你的影子 2024-08-09 03:21:22

您需要 GO，而不是 开始/结束。另外，您可能需要稍微编辑一下 EXISTS 查询，以确保获得正确的表：

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

如果您使用多个架构，则需要通过 sys.schemas 也进入检查。

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.

回复收藏 0 原文

~没有更多了~