SQL Update by Column Index

sql, sql-server

Solution

If you really need this, you could use the `INFORMATION_SCHEMA` table to find the columns and their order (using `ORDINAL_POSITION`). Then build a dynamic query with the update statement.

declare @columnNum int
SET @columnNum = 3

declare @column nvarchar(100)

set @column = 
(
    SELECT TOP 1
        COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Products'
        AND ORDINAL_POSITION = @columnNum
)

declare @sql nvarchar(500)

set @sql = 'Update Products Set ' + @column + ' = 54 where ProductID = 12947'

sp_executesql @sql

Problem

I'm using SQL Server and I have `Products` table with columns `ProductID, ProductName, ProductSalePrice, ProductBuyPrice` (there are many more columns tbh.) Usually you can update database like this : ``` Update Products Set ProductSalePrice = 54 where ProductID = 12947 ``` But what I want is, use update command with column index instead of column name. Like this : ``` Update Products Set "Third Column" = 54 where ProductID = 12947 ``` How to update table by column index? Any suggestions? Edit : it appears SQL Server doesn't have natural column order for query use. Which I wrongly guessed for my approach. I wanted to have a option, as like as the code above but without having work on database. Edit 2 : I've accepted the answer below since it seems impossible to do without work on database. If new approach comes, I may change the accepted answer. Thanks.

Original source