CODEWITHSUNDEEP
sqlsql-server
OM Asphyxiate
OM Asphyxiate

Reputation: 329

Update multiple tables with the same column

I'm updating a value in my DB for locations of my clinics. Right now I have clinic column across multiple tables that state the city they are in. Currently have 35 and it is compressed into a no space string (IE: FortSmith). I'm wanting to retroactively adjust each of these values with a different format and then use that format going forward with my appending. 

UPDATE transactions
SET clinic='07 - Fort Smith'
WHERE clinic='FortSmith'

The update is simple, however I have 35 different locations and a good amount of tables that share this column clinic Is there any way I can have this test against all tables for the column clinic and update where it applies?

Upvotes: 1

Views: 2327

Answers (2)

Faisal
Faisal

Reputation: 1397

You may use below statement to generate updates for all such tables and then copy those statements and execute them.

select 'UPDATE [' + o.[name] +
        '] SET clinic=''07 - Fort Smith''
        WHERE clinic=''FortSmith'''
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.[name] = 'clinic'

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

Addressing your need, rather than your exact question, the solution is normalization.

Create a Clinic table, with a unique numeric ClinicId, and a varchar ClinicName.

All your tables that have the clinic column, should instead have a ClinicId that is a foreign key referencing the ClinicId of the Clinic table.

All the queries that select the column Clinic from the pre-existing tables will need to be changed to include a JOIN to the table Clinic, to get the ClinicName column.

The payoff is that when the name of a Clinic changes, you would only need to change it in the Clinic table, instead of in 35 different tables. All the queries that get the Clinic name by joining to the Clinic table will automatically get the new Clinic name without any additional work on your part.

There can be valid reasons for denormalizing, but the trade-off is the problem you are facing now. Either normalize your database design, or execute 35 queries every time you need to change a clinic name.

Upvotes: 5

Related Questions