How can I reset all columns in a table beyond ordinal position x to 0?

Question

Lets imagine that I want to set all the columns after the first column id to 0 and that the column names in the table I would like to reset, they are defined in another table. To illustrate, lets imagine I am working with various currencies as defined in #tmpFX table. What is the SQL 2005 UPDATE command that allows me to set gbp, eur, jpy and usd all equal to 0 where ID = 2 based on a (SELECT * from #tmpFX) that identifies the columns that need updating?

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

i.e. row 2 of #tmpCashVal would read after the UPDATE command:

id  gbp eur jpy usd
2   0   0   0   0

Many thanks, Bertie.

For future reference, you would probably be better off just showing your current data and your desired results. Your question seems unnecessarily complex. — Abe Miessler, Aug 19 '11 at 16:40
Your design of #tmpCashVal should be CCYCode, Value. ID isn't needed. #tmpFX isn't needed. See database normalisation http://www.google.co.uk/search?q=database+normalisation — gbn, Aug 19 '11 at 16:52
If you can imagine each portfolio having its distinct currencies (i.e. variable), it seems to me the most flexible way of updating in a generic way as I do not want to hard code the currenciy fields into the UPDATE column when they can be inserted dynamically? Alsom the #tmpFX is actually the column names from information_schema.columns for the same table I am trying to update? — Bertie, Aug 19 '11 at 17:06
I'm with @gbn on this, I'm afraid - it can be done in they way you want, but the standard way of doing it would be something along the lines of what he suggests. — John N, Aug 19 '11 at 17:10
@Bertie: Then #tmpCashVal should be `PortfolioID (PK, FK), CCYCode (PK, FK), Value` Don't post bad design on a public forum and expect us to agree with you... — gbn, Aug 19 '11 at 17:15

score 0 · Answer 1 · edited May 23 '17 at 12:04

This will do the trick:

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

Obviously, you can change the @Query string to do whatever you like at this point.

You could also do this with a cursor, selecting each value out of #tmpFX in turn, and using dynamic SQL to execute it as here, but obviously you pay the performance penalty that goes with that.

For more on putting all the columns into one string, see Concatenate many rows into a single text string?

score 0 · Answer 2 · answered Aug 19 '11 at 17:15

There's no standard syntax that allows you to reference columns by their ordinal positions, not in Transact-SQL anyway.

You can obtain information about ordinal positions of columns by querying INFORMATION_SCHEMA.COLUMNS, though. And if your query must indeed be established around column ordinal positions, you'll need to build a dynamic query and supply it with the right column names to reference. Something like this, possibly:

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

teenboy · Answer 3 · 2011-08-19T19:25:40.483

Hope this helps...

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

How can I reset all columns in a table beyond ordinal position x to 0?

3 Answers3