如何在MYSQL服务器中使用单个JOIN语句更新两个表? [英] How to Update Two tables with single JOIN statement in MYSQL server?
问题描述
我已经读到一个JOIN语句可以连接两个表以进行更新，但是我很难绕开它.
I have read that A JOIN statement can connect two tables for update and I'm having a hard time warping my brain around it..
我有7张桌子，并且所有不同语言的所有字符集都在增长. 我需要更新并插入3列.
I have 7 tables and growing all different languages all different charsets. I need to update and insert 3 columns..
sudo命名
- table1EN
- table1DE
- table1ZH
- table1PT
- table1FR
- table1ES
- table1RU
表是100％相同的结构.
The tables are 100% the same structure..
现在要进行更新，我使用此查询
To do an update now I use this query
UPDATE `Table1EN` SET `Details`= 'String Value', `Name` = 'String Name',
`Info`= 1 WHERE `ID` = 1;
然后重复5000次，并将table1EN更改为table1 **并重新运行
and then repeat 5000 times and change table1EN to table1** and re-run that
有没有一种方法可以简化为?
Is there a way to simplify this to?
UPDATE `Table1EN`,`Table1ZH`,`Table1DE`,`Table1FR`,`Table1RU`,`Table1ES`,`Table1PT`
SET `Details`= 'String Value', `Name` = 'String Name', `Info`= 1 WHERE `ID` = 1;
运行此查询将返回模棱两可的名称详细信息，名称和信息. 使用join语句似乎会锁定服务器.
Running this query returns ambiguous name details, name and info. using a join statement seems to lock up the server..
我的问题是，如何在所有值都相同而没有任何变化的情况下运行多表更新查询?无法锁定服务器?没有模棱两可的名称错误吗?最终在重命名表后不必以5000的小卡运行查询吗?
My questions are how can I run a multi-table update query where all values are the same nothing changes? Not lock up the server? Not have an ambiguous name error? Finally not have to run the query in chucks of 5000 after renaming the table?
Update1:
Update1:
正如Arth在下面指出的那样，我没有在此处包括实际的JOIN查询.
As Arth has pointed below, I did not include the actual JOIN Query here.
UPDATE table1EN
INNER JOIN table1ZH USING (ID)
INNER JOIN table1DE USING (ID)
INNER JOIN table1FR USING (ID)
INNER JOIN table1PT USING (ID)
INNER JOIN table1ES USING (ID)
INNER JOIN table1RU USING (ID)
SET table1EN.Info = 1, table1EN.Details ='String Value',
table1ZH.Info = 1, table1ZH.Details ='String Value',
table1DE.Info = 1, table1DE.Details ='String Value',
table1FR.Info = 1, table1FR.Details ='String Value',
table1ES.Info = 1, table1ES.Details ='String Value',
table1RU.Info = 1, table1RU.Details ='String Value',
table1PT.Info = 1, table1PT.Details ='String Value'
WHERE table1EN.ID = 1;
我现在将其发布，希望简化它，以防止我每次尝试使用5000个不同查询运行它时服务器崩溃.
I'm posting it now in hopes to simplify it to stop the server from crashing anytime I try to run it with 5000 different query's at one time.
我已经尝试根据阅读的内容来减少这种情况
I have tried reducing this based on something I read
到
set table1EN.Info = 1, table1EN.Details ='String Value',
table1ZH.Info=table1EN.Info,
table1DE.Info=table1EN.Info,
table1FR.Info=table1EN.Info
etc ........
但是，这似乎会导致更多的服务器延迟，并导致我期望的崩溃.
However this seems to cause even more server lag and crashes witch I would expect..
推荐答案
首先，当您执行UPDATE JOIN时，您没有包括任何JOIN条件. /p>
First off when you do the UPDATE JOIN, you haven't included any JOIN conditions.. so that will try and do a cartesian product of each table:
#rows = #rows_table1EN * #rows_table1ZH * ... * #rows_table1PT
对于每个表，您都应在table1EN.id = table1xx.id上加入.
You should JOIN on table1EN.id = table1xx.id for each table.
下一个问题是，您将必须引用要更改的每一列.这将导致:
The next problem is that you will have to reference each column you want to change. This will result in:
SET table1EN.detail = 'String Value',
table1ZH.detail = 'String Value'
...
table1PT.detail = 'String Value'
这可以通过动态构建语句来完成，但这相当丑陋.
This could be done with dynamically building the statement, but that is fairly hideous.
这一切都使我对您的数据库结构产生疑问.您是否考虑过使用带有额外语言列的表?两个字母的标识符 (确定)还是语言表的外键(更好)?
This all leads me to question your database structure. Did you consider using one table with an extra language column; either the two letter identifier (OKish) or a foreign key to a languages table (better)?
这篇关于如何在MYSQL服务器中使用单个JOIN语句更新两个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
