-1

I'm using Workbench, I'm not an expert at SQL but always open to learning.

My goal is to Update multiple tables at once and have the Replace happen on all found results.

Something like:

SELECT * FROM *.*;
UPDATE *.* SET post_content = REPLACE(post_content,'data-exclude="direct"','');

But the syntax is wrong. I have 100+ databases that need to be updated so I'd rather not have to list each one out.

Also, if it helps, I'm editing the same table in each database, so if there's another way to go about this, awesome. The table has a different prefix in each database but always ends in: _posts

Thanks ahead of time!

user10129980
  • 13
  • 3
  • 1
    You'll have to write a script/procedure/code to write out and execute individual UPDATE statements to accomplish what you want. You can find all tables in your database that contain that particular column [like this](https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them) and then use that to write, dynamically, your UPDATE statements. Then execute them. – JNevill Jul 24 '18 at 20:28
  • 1
    Are these 100 different, independent db's or are they derived from 1 master production db/image? – dfundako Jul 24 '18 at 20:29

1 Answers1

1

As I noted in the comments, you will have to dynamically write out each UPDATE statement and then execute each dynamically generated statement. You can write a script or procedure to do that, or you can just write some SQL to identify databases/tables that contain the column name you are after and write out the UPDATE statements that way:

SELECT DISTINCT CONCAT('UPDATE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` SET post_content = REPLACE(post_content,''data-exclude="direct"'','''');') as statement
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'post_content';

Or something like that... (haven't tested)

Then just copy/paste the results back into your sql client and execute.

JNevill
  • 46,980
  • 4
  • 38
  • 63