Dynamic Update Statement with Variable Column Names

Dynamic update statement with variable column names

You can pass the name of the column in dynamic sql:

declare @sql nvarchar (1000);
set @sql = N'update table set ' + @column_name + '= ''''';

exec sp_executesql @sql;

Dynamic Column Name in SQL in Update statement

Yes, you have to concatenate the variable outside the string. In other words:

SET @sql = 'UPDATE [Table1] SET [Table1].[' + @columnName + '] = t1.Value ' +

EDIT: Another solution we have used is to replace tokens in the base sql to construct a new sql variable for execution.

DECLARE @sql nvarchar(max) = 'SELECT @ColumnName FROM @TableName';

DECLARE @sql2 nvarchar(max) = REPLACE(REPLACE(@sql,'@ColumnName',@ColumnNameVariable),'@TableName',@TableNameVariable)

EXEC (@sql2)

...Some code that changes the values of @ColumnNameVariable and @TableNameVariable...

DECLARE @sql2 nvarchar(max) = REPLACE(REPLACE(@sql,'@ColumnName',@ColumnNameVariable),'@TableName',@TableNameVariable)

EXEC (@sql2)

And you'll notice that the Declaration and Exec of SQL2 are exactly the same lines in both cases. This lends itself to use in a LOOP if that is applicable. (Except that you wouldn't DECLARE @Sql2 in the loop...just populate/re-populate it).

Need help updating a table with variable columns using dynamic SQL

In order to Insert the data into a column, first you need to ADD the column (with its datatype) to the table and because ALTER and UPDATE cannot be in the same batch you will have to use sp_executesql twice. So below is the query which will accomplish what you want.

DECLARE @NSQL NVARCHAR(1000),@ALTSQL NVARCHAR(1000), @CustomField NVARCHAR(50), @CustomFieldDataType NVARCHAR(50), @SQLSnippet NVARCHAR(500), @PayrollDate SMALLDATETIME
SET @PayrollDate = '10/15/17'
SELECT @CustomField = 'BatchID', @CustomFieldDataType = ' NVARCHAR(50)', @SQLSnippet = '''INC_'' + CAST(MONTH(@PayrollDate) AS VARCHAR)'
SET @ALTSQL = 'ALTER TABLE #MyTempTable ADD '+ @CustomField + @CustomFieldDataType 
SET @NSQL = 'UPDATE #MyTempTable SET '+ @CustomField +' = '+ @SQLSnippet

EXECUTE sp_executesql @ALTSQL, N'@CustomField NVARCHAR(50), @CustomFieldDataType NVARCHAR(50)',@CustomField, @CustomFieldDataType
EXECUTE sp_executesql @NSQL, N'@CustomField NVARCHAR(50), @SQLSnippet NVARCHAR(500),@PayrollDate SMALLDATETIME',@CustomField, @SQLSnippet,@PayrollDate


SELECT * FROM #MyTempTable

How to use dynamic column names in an UPDATE statement in a function?

Some dynamic SQL will help. The select and update queries are taken from the question w/o change.

drop function if exists aa.clean_data();
create or replace function aa.clean_data() returns void language plpgsql as
$$
declare
    UPDATE_A constant text := $dynsql$ UPDATE autopipe.test SET %I = initcap(%I) $dynsql$;
    UPDATE_B constant text := $dynsql$ UPDATE autopipe.test SET %I = REGEXP_REPLACE(%I, '[^a-zA-Z\d\s:/\//]', '', 'g') $dynsql$;
    UPDATE_C constant text := $dynsql$ UPDATE autopipe.test SET %I = REGEXP_REPLACE(%I, '\s+$', '', 'g') $dynsql$;
    col_name text;
begin
    for col_name in select column_name from information_schema.columns where table_schema = 'aa' and table_name = 'test' and data_type in ('character varying', 'character','text', '"char"', 'name') loop
        execute format(UPDATE_A, col_name, col_name);
        execute format(UPDATE_B, col_name, col_name);
        execute format(UPDATE_C, col_name, col_name);
    end loop;
end;
$$;

It might be worth running a VACCUM on the target table after such a massive UPDATE.

Update table from temp table with dynamic columns names in SQL Server

You can store the matching column name into a variable and use T-SQL to construct a query using the name stored in the variable.

DECLARE @col_name varchar(128), @sql_stmt varchar(1000)
SET @col_name = (
    SELECT name
    FROM tempdb.sys.columns
    WHERE object_id = Object_id('tempdb..#YourTempTableName')
    and name in (
        SELECT column_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = N'YourTableName'
        )
    )

SET @sql_stmt = ('
    UPDATE YourTableName 
    SET ' + @col_name + ' = t2.' + @col_name +'
    FROM YourTableName t1
    JOIN #YourTempTableName t2 on t1.id = t2.id'
    )

EXEC @sql_stmt

Replace #YourTempTableName and YourTableName with the actual table names. Please note that this approach only works if you have only one column name between the two tables that matches. If it finds more than one match, this query will throw an error. Also, this assumes that both id columns in your tables are called 'id', if not you will have to update the join statement with the actual id column names.

How to use dynamic column names in an UPDATE or SELECT statement in a function?

In an UPDATE statement in PL/pgSQL, the table name has to be given as a literal. If you want to dynamically set the table name and the columns, you should use the EXECUTE command and paste the query string together:

EXECUTE 'UPDATE ' || quote_ident(r.relname) ||
       ' SET ' || quote_ident(r.cols_list[1]) || ' = $1, ' || 
                  quote_ident(r.cols_list[2]) || ' = $2' ||
       ' WHERE ' || quote_ident(r.cols_list[1]) || ' = $3 AND ' ||
                    quote_ident(r.cols_list[2]) || ' = $4'
USING ncicd9, ncdesc, ocicd9, ocdesc;

The USING clause can only be used for substituting data values, as shown above.

how to create a dynamic update query with variables?

You need dynamic SQL, not parameters. You can't parameterize column names or table names. So something like:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @COLUMN1 NVARCHAR(10) = 'USER_ID8'
DECLARE @COLUMN2 NVARCHAR(10) = 'USER_ID6'
DECLARE @TABLENAME NVARCHAR(10) = 'POLICYREF'

SET @SQL = 'UPDATE TL
            SET '+ quotename(@COLUMN1) + '= AB.COLUMN1,'
                 + quotename(@COLUMN2) + '= AB.COLUMN2
            FROM ' + quotename(@TABLENAME) + ' TL
            JOIN ABACUS AB
            ON TL.REF = AB.REF
            AND TL.SUBS = AB.SUBS
            WHERE ' + quotename(@COLUMN1) + ' IS NULL
            AND ' + quotename(@COLUMN2) +' IS NULL';

EXEC (@SQL)

SET @TABLENAME NVARCHAR(10) = 'USERREF'
SET @SQL = 'UPDATE TL
            SET '+ quotename(@COLUMN1) + '= AB.COLUMN1,'
                 + quotename(@COLUMN2) + '= AB.COLUMN2
            FROM ' + quotename(@TABLENAME) + ' TL
            JOIN ABACUS AB
            ON TL.REF = AB.REF
            AND TL.SUBS = AB.SUBS
            WHERE ' + quotename(@COLUMN1) + ' IS NULL
            AND ' + quotename(@COLUMN2) +' IS NULL';


EXEC (@SQL)

---

Related Topics