0

I can update the value in the table separately and I am able to filter the tables as I want, but I'm having trouble combining the two

I've looked at running a sql query on another one but was only able to find something like this.

SELECT <SOMETHING HERE> FROM
(
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'table&example' 
) AS SUBQUERY

  1. This is how I'm updating the value in the table. UPDATE `table_1_example` SET `comment_status` = 'closed' WHERE `post_type` = 'attachment' AND `comment_status` = 'open';

  2. This is how I can get all the tables I need SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'table%example'

Edit:

I also just found this post which explains how to update multiple identical columns over many tables.

And they did something like 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'

though I'm not really sure what they mean by query window... I'm using MySQL workbench by the way.

I got this figured out now using the above script I made something that looks like this.

SELECT 'UPDATE ' + TABLE_NAME + ' SET comment_status = ''closed'' WHERE post_type = ''attachment'' AND comment_status = ''open'';'
 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'test&example'
cuppajoeman
  • 252
  • 5
  • 13
  • 1
    The script you mentioned will create another script for you, then you will take the new script and run it manually in the query editor. – J.K Jul 10 '19 at 15:32
  • Not sure what you mean by a script... When I run the query to get the tables, then I get a huge result grid. Am I supposed to paste this whole grid straight into the window, then beneath that write the command which is in my edit? @J.K – cuppajoeman Jul 10 '19 at 15:36
  • 1
    SQL script is a set of SQL commands like the one you mentioned in the edit. If you want to update the same column in many tables, then the SQL command in the edit will give you an SQL script, one command for each table, something like : Update Table1 Set CreatedDateTime = '' – J.K Jul 10 '19 at 15:50
  • Ah, I see so we are generating a SQL script with another! – cuppajoeman Jul 10 '19 at 15:53
  • 1
    Exactly, you generate the script, then run it. That's why he said "one time task" :) – J.K Jul 10 '19 at 15:55

0 Answers0