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
Advertisement
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 …