UPDATE specified multiple tables with the same column name

Question

I have multiple tables with matching column names and want to make a update statement affecting 1 column in 4 specific tables like this:

UPDATE table1, table2, table3, table4
SET table1.column_1 = 'value', table2.column_1 = 'value', table3.column_1 = 'value', table4.column_1 = 'value'
WHERE table1.column_id = 'value' OR table2.column_id = 'value' OR table3.column_id = 'value' OR table4.column_id = 'value'

This is not working, and i googled many hours for an answer, and ive found this https://stackoverflow.com/a/19797529/1824324:

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

And with a little tweaking:

SELECT 'UPDATE ' + TABLE_NAME + ' SET column_1= ''Value'' where column_id=''value'''
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'column_1'

Now my problem is i don't want every table with that particular column to be updated, only from table1, table2, table3, and table4. How do i cut the "FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'column_1'" and replace with the 4 tables?

Which is it... mysql or sql-server? Please edit tags so only relevant one remain. — Bohemian, Jan 10 '17 at 22:55
Why do you have muliple tables with matching column names? :-( — Strawberry, Jan 10 '17 at 23:28
@ Strawberry - The 4 tables have different columns except user_id, and some other stuff — MichaelJorgensen5000, Jan 11 '17 at 03:15

score 2 · Accepted Answer · answered Jan 10 '17 at 22:58

2

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')

answered Jan 10 '17 at 22:58

jfneis

2,139
18
31

Works, thank you, but is it possible to do this without going through every columns in every table from the "INFORMATION_SCHEMA.COLUMNS" ? – MichaelJorgensen5000 Jan 10 '17 at 23:04
It is filtering the tables and columns down to your list, and creating the SQL statement only for the relevant ones. There is really no way to retrieve filtered rows from a table without going through the table, or it's index. – Raj More Jan 10 '17 at 23:10
@Raj More said it: you are filtering records, and databases are really good on it! – jfneis Jan 11 '17 at 00:09
i get truncated incorrect double value,is it because my column are double why is it so ? – babbin tandukar Feb 15 '23 at 10:47

UPDATE specified multiple tables with the same column name

1 Answers1