sql - 动态更新问题
问题描述
我有一个表,我们在该表中将其称为“table_X” 我有多个列 (46),将来我们可能会将其扩展为更多列,因为表的来源是旧的 ERP 系统,在某些情况下我们需要对数据集进行转换,其中一种转换是当我们将 '' 值替换为 NULL 时,这是我遇到问题的地方,我写了一个动态更新,因为前面提到的原因(将来我们将有更多列),但我收到错误消息,现在我被卡住了。
DECLARE @SQL_columnnull NVARCHAR(max)
DECLARE @db2 NVARCHAR(max)
DECLARE @table2 NVARCHAR(max)
SET @db2 = 'db'
SET @table2 = 'table_X'
SELECT @SQL_columnnull_part_1 = STRING_AGG(CAST( N' UPDATE '+@db2+'.[dbo].'+@table2+' WITH (TABLOCK) SET ' AS NVARCHAR(MAX))
+QUOTENAME(COLUMN_NAME,'['']') + N' = NULL WHERE '
+QUOTENAME(COLUMN_NAME,'['']') ,+ N' = '''';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION <= 3
对于前两列,代码能够正确填充命令部分,但是当它到达最后一列时,“='';” 不会被填充
UPDATE db.[dbo].table_X SET [Column_1] = NULL WHERE [Column_1] = '';
UPDATE db.[dbo].table_X SET [Column_2] = NULL WHERE [Column_2] = '';
UPDATE db.[dbo].table_X SET [Column_3] = NULL WHERE [Column_3]
解决方案
你在搞乱你的 STRING_AGG
语法是
STRING_AGG ( expression, separator )
你的分隔符是
+ N' = '''';'
由于在最后一个条目之后没有应用分隔符,因此您会得到您看到的结果!
我也会对演员表保持警惕,您将表达式的开头转换为 nvarchar(max),但是您是串联的非 varchar 字符串。
最后 - 你为什么要为每一列单独更新?这是非常糟糕的表现!
首先,执行您想要的固定查询将是:
SELECT
@SQL_columnnull =
STRING_AGG(
CAST(
' UPDATE ' + @db2 + '.[dbo].' + @table2 + ' WITH (TABLOCK) SET '
+ QUOTENAME(COLUMN_NAME, '['']') + N' = NULL WHERE '
+ QUOTENAME(COLUMN_NAME, '['']') +N' = '''''
AS NVARCHAR(MAX))
,';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION <= 3
我已经在演员表中包含了完整的字符串,分隔符现在只是“;”
但是,为了提高性能,我会这样做:
SELECT
@SQL_columnnull =
N' UPDATE ' + @db2 + '.[dbo].' + @table2 + ' WITH (TABLOCK) SET ' +
STRING_AGG(
CAST(
QUOTENAME(COLUMN_NAME, '['']') + N'='+N'IIF('+ QUOTENAME(COLUMN_NAME, '['']') + N'= '''',NULL,'+ QUOTENAME(COLUMN_NAME, '['']')+') '
AS NVARCHAR(MAX))
,',
')
+'
WHERE '+
STRING_AGG(
CAST(
QUOTENAME(COLUMN_NAME, '['']') + N'= '''' '
AS NVARCHAR(MAX))
,' OR ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
AND INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION <= 3
这只会遍历您的表,并更新具有错误数据的列。最后,我会检查所有列是否都是 varchar 或 nvarchar 数据类型,以排除可能会出错的其他数据类型的列
这给
UPDATE db.[dbo].table_X
SET [Column_1] = IIF([Column_1] = '', NULL, [Column_1])
,[Column_2] = IIF([Column_2] = '', NULL, [Column_2])
,[Column_3] = IIF([Column_3] = '', NULL, [Column_3])
WHERE [Column_1] = ''
OR [Column_2] = ''
OR [Column_3] = ''