CODEWITHSUNDEEP
mysqlsql
MichaelJorgensen5000
MichaelJorgensen5000

Reputation: 47

UPDATE specified multiple tables with the same column name

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?

Upvotes: 1

Views: 2785

Answers (1)

jfneis
jfneis

Reputation: 2197

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

Upvotes: 2

Related Questions