Update based on select from INFORMATION_SCHEMA

Question

I have this query:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name LIKE 'organization_id'

Based on the result, I want to update all the returned tables with 'organization_id' = something; Something like this:

UPDATE (above query results) SET `organization_id` = 'something'

Answer 1

That's a job for Dynamic SQL .

Try this:

SELECT DISTINCT CONCAT('UPDATE ',t.table_name,' SET organization_id = <ID> ;')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'organization_id'

Then copy the output and execute it.