时间戳

首页 > 解决方案 > 我可以根据包含列上表名的另一个表来更新许多表吗？

sql - 我可以根据包含列上表名的另一个表来更新许多表吗？

问题描述

目前正在从事一个项目，我必须更新 85 个表上的数据，将当前的空字符串替换为 NULL 值。这是一个简单的 SQL 查询，但由于它是一个敏感的环境，如果出现任何问题，我们需要恢复它。

主要思想是创建一个表来保存要回滚的数据。但我试图避免创建 85 个表。

我将举一个更小的例子：

有 4 张桌子


   ------------------------------------
   |            airplane              |
   ------------------------------------
   | air_ID | color | tail_number     |
   ------------------------------------
   |  1     | red   |                 |
   |  2     | green |                 |
   |  3     | black |  21AF           |
   ------------------------------------
   ------------------------------------
   |            bus                   |
   ------------------------------------
   | bus_ID | color | tag_number      |
   ------------------------------------
   |  1     | red   |  AAY-464        |
   |  2     | green |                 |
   |  3     | black |                 |
   ------------------------------------
   ------------------------------------
   |            train                 |
   ------------------------------------
   | tr_ID  | color | designated_name |
   ------------------------------------
   |  1     | red   |  99212          |
   |  2     | green |                 |
   |  3     | black |                 |
   ------------------------------------
   ------------------------------------
   |         Cruise_Ship              |
   ------------------------------------
   | sea_ID | color | hull_number     |
   ------------------------------------
   |  1     | red   |                 |
   |  2     | green |  MAGDA          |
   |  3     | black |                 |
   ------------------------------------

所以我用数据创建了一个临时表


    -------------------------------------------------
    |         update_table                          |
    -------------------------------------------------
    | table_name | ID_colname | ID  |  col_name     |
    -------------------------------------------------
    |   airplane |   air_ID   |  1  | tail_number   |
    |   airplane |   air_ID   |  2  | tail_number   |
    |    bus     |   bus_ID   | 2   | tag_number    |
    |    bus     |   bus_ID   | 3   | tag_number    |
    |    train   |   tr_ID    |  2  |designated_name|
    |    train   |   tr_ID    |  3  |designated_name|
    |Cruise_Ship |   sea_ID   |  1  |  hull_number  |
    |Cruise_Ship |   sea_ID   |  3  |  hull_number  |
    -------------------------------------------------

使用此表，我试图生成一个动态 SQL 来一次调用更新所有表


    SET @SQLString = N'UPDATE @table 
        SET  @value = '+ @empty +'
        where @key = @id';

    SET @ParmDefinition = N'@table nvarchar(max),
        @value nvarchar(max) ,
        @key nvarchar(max) ,
        @id int';

    DECLARE @table nvarchar(255)
    DECLARE @value nvarchar(255)
    DECLARE @key nvarchar(255)
    DECLARE @id int

    select @table = table_name, @id = ID, @key = ID_colname , @value = col_name from update_table

    EXECUTE sp_executesql
        @SQLString
        ,@ParmDefinition
        ,@table
        ,@value
        ,@key
        ,@id
        ;

但这不起作用，有人知道如何改进这个查询吗？

这是一个高调的环境，开发人员不是执行代码的人，因此需要客户证明。代码在一夜之间运行，以免干扰白天的操作。

标签： sqlsql-serverdynamic-sql

解决方案

根据SQL Server 文档：

如果 SELECT 语句返回多行且变量引用非标量表达式，则该变量将设置为结果集中最后一行中表达式的返回值。

这意味着您的变量仅分配有最后一行的值。因此，只有 [Cruise_Ship].[hull_number] 将被传递给 sp_executesql，这是您的脚本正在更新的唯一列。

要存储多个值，应使用表变量。

sp_executesql 确实接受参数，它用于构建动态查询。

但是我认为您不能将表值变量作为参数传递。

补充：检查如何将表值变量传递给 sp_executesql。

这是您的代码出错的地方。

select @table = table_name, @id = ID, @key = ID_colname , @value = col_name from update_table

我知道这并不优雅，但下面的代码应该可以完成这项工作。无论您是否进行备份，我都建议将其包装在 TRANSACTION 中。

DECLARE @empty NVARCHAR(10) 
SET @empty = 'NULL'

SELECT
    'UPDATE ' + s.name + '.' + t.name + ' 
    SET [' + c.name + '] =  ' + @empty + ' 
    WHERE LTRIM([' + c.name + ']) = ''''
    END;'
FROM sys.tables t
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
INNER JOIN sys.columns c
    ON t.object_id = c.object_id
ORDER BY
    s.name
   ,t.name
   ,c.column_id

推荐阅读