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

Question

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

"several tables .... named "user" . It is unclear what you are referring to because a tablename in a Schema/Database is unique. — Luuk, Apr 04 '21 at 15:20
@Luuk sorry I meant the database name was user. lemme fix that — i am bad at coding, Apr 04 '21 at 15:21

score 1 · Accepted Answer · answered Apr 04 '21 at 15:40

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 ...

sorry couldn't find anything related, didn't know what it was called, only this was showing up: https://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server. thank you very much — i am bad at coding, Apr 04 '21 at 15:51

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

1 Answers1