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 with .dbo.sun_001 to .dbo.sun_020.
In every .dbo.sun_001 to dbo.sun.020 tables there is one column name "server" that I would like to change the value from Dns02 -> Dns04 for all the row.
Any advise on a single to two command to do it in the most simplest way? Appreciate very much.
Only 20 tables, Copy & Paste 19 times SQL code and change table name will faster than find a "simplest way" 😂jenson245
- ... and will be more safe.
If the column is part of a unique index, the update may (will) fails.
- 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.
