如何将表中序数位置 x 之外的所有列重置为 0？

Question

假设我想将第一个列 id 之后的所有列设置为 0，并且我想重置表中的列名称，它们是在另一个表中定义的。为了说明这一点，让我们假设我正在使用 #tmpFX 表中定义的各种货币。 SQL 2005 UPDATE 命令是什么，允许我根据标识需要的列的 (SELECT * from #tmpFX) 将 gbp、eur、jpy 和 usd 全部设置为 0，其中 ID = 2更新？

use tempdb
GO
CREATE TABLE #tmpFX(iso_code VARCHAR(3))
INSERT #tmpFX VALUES('gbp')
INSERT #tmpFX VALUES('eur')
INSERT #tmpFX VALUES('jpy')
INSERT #tmpFX VALUES('usd')
SELECT * FROM #tmpFX

CREATE TABLE #tmpCashVal (id INT, gbp REAL, eur REAL, jpy REAL, usd REAL)
INSERT #tmpCashVal VALUES (1, 0, 0, 0, 1000)
INSERT #tmpCashVal VALUES (2, 0, 0, 2000, 0)
INSERT #tmpCashVal VALUES (3, 500, 0, 0, 0)

SELECT * FROM #tmpFX
SELECT * FROM #tmpCashVal

DROP TABLE #tmpFX
DROP TABLE #tmpCashVal

即 #tmpCashVal 的第 2 行将在 UPDATE 命令后读取：

id  gbp eur jpy usd
2   0   0   0   0

非常感谢， 伯蒂。

Answer 1

这就能解决问题：

--This bit puts everything in one string
DECLARE @Columns VARCHAR(MAX)
SELECT @Columns = COALESCE(@Columns + ' = 0, ', '') + iso_code FROM #tmpFX
SET @Columns = @Columns + ' = 0'

--Build our query string
DECLARE @Query VARCHAR(MAX)
SET @Query = 'UPDATE #tmpCashVal SET ' + @Columns + ' WHERE id = 2'

EXEC(@Query)

显然，您现在可以更改 @Query 字符串来执行您喜欢的任何操作。

您还可以使用游标来执行此操作，依次选择 #tmpFX 中的每个值，并使用动态 SQL 来执行它，但显然您会付出随之而来的性能损失。

有关将所有列放入一个字符串的更多信息，请参阅将多行连接到单个文本字符串？

Answer 2

没有标准语法允许您按序号位置引用列，无论如何在 Transact-SQL 中也是如此。

不过，您可以通过查询INFORMATION_SCHEMA.COLUMNS来获取有关列序数位置的信息。如果您的查询确实必须围绕列序数位置建立，则您需要构建一个动态查询并为其提供正确的列名称以供引用。像这样的事情，可能是：

DECLARE @sql varchar(max), @Columns varchar(max), @TableName sysname;
SET @TableName = 'you table name';

SELECT @Columns = COALESCE(@Columns + ', ', '') + QUOTENAME(COLUMN_NAME) + ' = 0'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
  AND ORDINAL_POSITION > the specified position;

SET @sql = 'UPDATE ' + QUOTENAME(@TableName) + ' SET ' + @Columns + ' WHERE ID = 2';

PRINT @sql;
-- EXEC(@sql);

Answer 3

希望这有帮助...

use tempdb
GO
CREATE TABLE #tmpFX(iso_code VARCHAR(3))
INSERT #tmpFX VALUES('gbp')
INSERT #tmpFX VALUES('eur')
INSERT #tmpFX VALUES('jpy')
INSERT #tmpFX VALUES('usd')
SELECT * FROM #tmpFX

CREATE TABLE #tmpCashVal (id INT, gbp REAL, eur REAL, jpy REAL, usd REAL)
INSERT #tmpCashVal VALUES (1, 0, 0, 0, 1000)
INSERT #tmpCashVal VALUES (2, 0, 0, 2000, 0)
INSERT #tmpCashVal VALUES (3, 500, 0, 0, 0)

SELECT * FROM #tmpFX
SELECT * FROM #tmpCashVal

DECLARE @ID AS INT = 2

DECLARE @Update_Cols AS NVARCHAR(MAX) = ''
DECLARE @SqlText AS NVARCHAR(MAX) = ''

SELECT @Update_Cols = @Update_Cols + '[' + iso_code + '] = 0,' FROM #tmpFX

SELECT @Update_Cols AS 'Cols'

SELECT @Update_Cols = LEFT(@Update_Cols, CASE WHEN LEN(@Update_Cols) > 0 THEN LEN(@Update_Cols) - 1 ELSE 0 END)

--Do only if atleast one row exists in the table #tmpFX
IF LEN(@Update_Cols) > 0 
BEGIN
    SELECT @SqlText = 'UPDATE #tmpCashVal SET ' + @Update_Cols + ' WHERE ID = @ID'
    EXEC sp_executesql @SqlText, N'@ID INT', @ID = @ID
END
SELECT * FROM #tmpCashVal

DROP TABLE #tmpFX
DROP TABLE #tmpCashVal