MySQL 如何通过单个mysql查询更新多个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20208350/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update multiple tables by single mysql query?
提问by stockBoi
I have two tables tb1 & tb2 I have to update a common column of both tables, i.e user_level I have a common criteria for both tables like username.
我有两个表 tb1 和 tb2 我必须更新两个表的公共列，即 user_level 我有两个表的共同标准，如用户名。
So I want to update like this:
所以我想像这样更新：
UPDATE tb1, tb2 SET user_level=1 WHERE username="Mr.X"
But somehow it is not working. What would be the correct mysql query for this?
但不知何故它不起作用。什么是正确的 mysql 查询？
回答by Hiral Pandya
Try this:
尝试这个：
UPDATE table_1 tb1,
table_2 tb2,
table_3 tb3
SET tb1.field2 = "some value",
tb2.field3 = "some other value",
tb3.field4 = "some another value"
WHERE tb1.field1 = tb2.field1
AND tb1.field1 = tb3.field1
AND tb1.field1 = "value"
I tested the code on MSAccess and SQL SERVER 2008
我在 MSAccess 和 SQL SERVER 2008 上测试了代码
回答by Abhik Dey
Your problem is solved,just follow this what I have done-
您的问题已解决，只需按照我所做的操作-
create table tb1(user_level int); create table tb2(user_level int,username varchar(20));
创建表 tb1(user_level int); 创建表 tb2(user_level int,username varchar(20));
insert into tb1 values(2); insert into tb2 values(2,'Mr.X');
插入 tb1 值（2）；插入 tb2 values(2,'Mr.X');
I have this two tables like this where user_level is common,now according to you I tried to update the user_level column in both table using one query on a common criteria for both table i.e. username.
我有两个这样的表，其中 user_level 是常见的，现在根据您的说法，我尝试使用一个查询来更新两个表中的 user_level 列，该列对两个表的通用条件（即用户名）进行查询。
I tried to update the value of user_level column from 2 to 3 in both tables where the username is 'Mr.X' using a single query,so I tried the following query and it perfectly worked..
我尝试使用单个查询将两个表中 user_level 列的值从 2 更新为 3，其中用户名是“Mr.X”，因此我尝试了以下查询，并且效果很好。
update tb1 inner join tb2
on tb1.user_level=tb2.user_level
set tb1.user_level=3,
tb2.user_level=3
where tb2.username='Mr.X' ;
回答by Jorge Campos
Try this:
尝试这个：
update db1 inner join db2 on db1.username = db2.username
set db1.user_level = 1,
db2.user_level = 1
where db1.username = 'a';
See it here on fiddle: http://sqlfiddle.com/#!2/ba34ac/1
回答by Asuquo12
The correct query is that you have to specify the full table and row/column you are trying to update in the two tables and indeed database if you are updating across databases too.
正确的查询是您必须指定要在两个表中更新的完整表和行/列，如果您也在跨数据库更新，则必须指定数据库。
typical example:
典型例子：
UPDATE tb1, tb2 SET tb1.username = '$username', tb2.location ='New York'WHERE tb1.userid = '$id' AND tb2.logid = '$logid'
回答by Premjith
We can update it without join like this
我们可以像这样在没有加入的情况下更新它
UPDATE table1 T1, table2 T2
SET T1.status = 1 ,T2.status = 1
WHERE T1.id = T2.tab1_id and T1.id = 1
We can update it with join like this
我们可以像这样使用 join 更新它
UPDATE table1
INNER join table2 on table1.id=table2.tab1_id
SET table1.status=3,table2.status=3
WHERE table1.id=1
