1

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?

OM Asphyxiate
  • 329
  • 1
  • 5
  • 14
  • Possible duplicate of [Find all tables containing column with specified name](http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name) – Tab Alleman Jan 27 '16 at 18:27
  • I know the tables that have it, I'm just looking for a more efficient way than using the same query with just a table name adjustment each time (x35 for each different location) – OM Asphyxiate Jan 27 '16 at 18:50
  • There is no way to update more than one table in a single SQL command, so there is no choice but to run one update statement for each table you want to update. – Tab Alleman Jan 27 '16 at 18:56

2 Answers2

5

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • To add to this, if for some reason you DO need to keep the data denormalized, it might be better to hammer out a stored proc with the queries required to change the data in question across the board, that way you have something that's reusable in the future. – user2366842 Jan 27 '16 at 19:52
0

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'
Faisal
  • 1,307
  • 1
  • 10
  • 16