Bulk alter SQL column data value in MS-SQL2019 in trans-sql

SQL Server Administration
1

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

2

Can you run the below query and confirm if the output matches your target tables and column?

select TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
and TABLE_NAME like 'sun_%'
and COLUMN_NAME='server'

Just want to know if those are the only tables that match your criteria.
As per the table naming, I guess there must be 20 tables starting from 001 to 020.

3 

USE abcDb;

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sun_001' AND schema_id = 1)
    EXEC('UPDATE s SET server = ''Dns04'' FROM dbo.sun_001 s WHERE server = ''Dns02'''); /*if entire column value = 'Dns02'*/
    /* or EXEC('UPDATE s SET server = REPLACE(server, ''Dns02'', ''Dns04'') FROM dbo.sun_001'''); /*if column contains 'Dns04' with other text*/*/

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sun_002' AND schema_id = 1)
    EXEC('UPDATE s SET server = ''Dns04'' FROM dbo.sun_002 s WHERE server = ''Dns02''');

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sun_003' AND schema_id = 1)
    EXEC('UPDATE s SET server = ''Dns04'' FROM dbo.sun_003 s WHERE server = ''Dns02''');

IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sun_004' AND schema_id = 1)
    EXEC('UPDATE s SET server = ''Dns04'' FROM dbo.sun_004 s WHERE server = ''Dns02''');

/*...*/
4

Hi Scott,
Thanks for your command. The command was executed successfully but I don't see the changes on the "server" column the value of DNS02 still remains there.

5

HI Scott,
Thanks for your command. This command don't seem to bring any changes to the value on the "server" column.