Is it possible to search all tables in MySQL workbench database with java preparedstatement SQL in order to change a value?

I have several tables like this in a Schema/Database (the Schema is called “user”) in MySQL workbench:

----Table 1----
User       Score
John       5
Sarah      3

----Table 2----
User       Score
John       5

----Table 3----
User       Score
John       5
Sarah      3

Suppose Sarah gets a score change to 5. I want to update all of her scores in all of the tables in which she exists, to be 5. So then the 3 tables would look like this:

 ----Table 1----
User       Score
John       5
Sarah      5

----Table 2----
User       Score
John       5

----Table 3----
User       Score
John       5
Sarah      5

Is this possible to do using a java prepared SQL statement? If so, how would I do it? Thanks in advance. I can’t seem to find anything on this. One way I know would work is searching for each table, putting the table names in an arraylist and then searching each one in a loop. But I’m not sure that’s the best approach for this.

I was thinking something like this, if it were possible (some awkward psuedo-code):

SELECT ALL_TABLES WHERE user = "SARAH" UPDATE score = 5

Answer

It looks like you need to build your statement dynamically like this:

set @user="SARAH";
set @score=5;
select 
   CONCAT("UPDATE `",c1.TABLE_NAME,"` SET score=",@score," WHERE user='",@user,"'") as statement  
from 
   information_schema.columns c1
inner join
   information_schema.columns c2 on c1.TABLE_SCHEMA=c2.TABLE_SCHEMA
                                and c1.TABLE_NAME=c2.TABLE_NAME
                                and c2.COLUMN_NAME='score'
where c1.TABLE_SCHEMA='test' and c1.COLUMN_NAME='user'
;

output can be something like this:

+-------------------------------------------------+
| statement                                       |
+-------------------------------------------------+
| UPDATE `table 1` SET score=5 WHERE user='SARAH' |
| UPDATE `table 2` SET score=5 WHERE user='SARAH' |
| UPDATE `table 3` SET score=5 WHERE user='SARAH' |
+-------------------------------------------------+

How to Execute Dynamic Sql String as a Query in mysql server? was asked before …