I Have the Same Column in Multiple Tables, and Want to Update That Column in All Tables to a Specific Value. How to Do This

I have the same column in multiple tables, and want to update that column in all tables to a specific value. How can I do this?

If that's a one time task, just run this query, copy & paste the result to query window and run it

Select 'UPDATE ' + TABLE_NAME + ' SET CreatedDateTime = ''<<New Value>>'' '
From INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'CreatedDateTime'

UPDATE specified multiple tables with the same column name

I believe you are talking about MySQL due to the tables you are using.

Simply filter the TABLE_NAME column:

SELECT 'UPDATE ' + TABLE_NAME + ' SET column_1= ''Value'' where column_id=''value'''
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'column_1'
AND TABLE_NAME IN ('table1', 'table2')

Update multiple tables with the same column

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.

Update single column found in multiple tables

Try like this

SELECT 'UPDATE ' + TABLE_NAME + ' SET customer= ''NewCustomerValue'' where customer=''xxxx'''
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'customer'

SQL - Update multiple tables, the same column across one query

I think this does what you want. The idea is first update table1, and to use the returning clause to return the table1_id, that can be used to update the two other tables:

with 
    t1 as (
        update table1
        set active = true
        where table1_name = 'Digital Only'
        returning table1_id
    ),
    t2 as (
        update table2
        set active = true
        from t1
        where table1_fk = t1.table1_id
    )
update table3
set active = true
from t1
where table1_fk = t1.table1_id

Multiple Table Update - Same Column For Each Table

Yes, you'll need to qualify each table / column that you want to update if they use the same logical names.

The query sees you're looking for a logical name of 'status', but you've defined 'status' to be the logical name for 3 different physical locations in the database. Which one did you mean? There are many things that can be done when multiple tables are involved. So for a database to assume you want to update all of them would be very bad.

---

Related Topics