Forum Discussion
Bulk alter SQL column data value in MS-SQL2019
Complete command to alter SQL column data value. Hi, A newbie here, how to bulk update data in SQL column? For example my database name is "abcDB" I have multiple tables in "abcDb" that start ...
Switching Database Context: USE abcDB; ensures that the commands run in the context of your target database.
Variable Declarations:
@i is used as a loop counter.
@sql will hold the dynamically generated SQL command.
@tableName will store the current table name being processed.
@newValue is the new value you want to set for column_name.
Looping Through Tables:
The loop runs from 1 to 20 to cover tables named dbo.sun_001 to dbo.sun_020.
SET @tableName = 'dbo.sun_00' + RIGHT('0' + CAST(@i AS VARCHAR), 2); constructs the table name dynamically.
SET @sql = N'UPDATE ' + @tableName + ' SET column_name = @newValue WHERE some_condition = ''NFT_bermudaunicorn.com'''; constructs the SQL update statement.
EXEC sp_executesql @sql, N'@newValue NVARCHAR(100)', @newValue; executes the constructed SQL command.
Condition Example: The WHERE some_condition = 'NFT_example' part is an example of how you might filter which rows to update, e.g., updating records related to NFTs.
This script updates the column_name in all specified tables with the new value, demonstrating how dynamic SQL can be used for bulk updates across multiple tables.
Feel free to modify the @newValue, column_name, and some_condition to match your actual requirements. Always ensure to backup your database before running bulk updates.
Variable Declarations:
@i is used as a loop counter.
@sql will hold the dynamically generated SQL command.
@tableName will store the current table name being processed.
@newValue is the new value you want to set for column_name.
Looping Through Tables:
The loop runs from 1 to 20 to cover tables named dbo.sun_001 to dbo.sun_020.
SET @tableName = 'dbo.sun_00' + RIGHT('0' + CAST(@i AS VARCHAR), 2); constructs the table name dynamically.
SET @sql = N'UPDATE ' + @tableName + ' SET column_name = @newValue WHERE some_condition = ''NFT_bermudaunicorn.com'''; constructs the SQL update statement.
EXEC sp_executesql @sql, N'@newValue NVARCHAR(100)', @newValue; executes the constructed SQL command.
Condition Example: The WHERE some_condition = 'NFT_example' part is an example of how you might filter which rows to update, e.g., updating records related to NFTs.
This script updates the column_name in all specified tables with the new value, demonstrating how dynamic SQL can be used for bulk updates across multiple tables.
Feel free to modify the @newValue, column_name, and some_condition to match your actual requirements. Always ensure to backup your database before running bulk updates.