MySql Update A Joined Table
I want to update a table in a statement that has several joins. While I know the order of joins doesn't really matter (unless you you are using optimizer hints) I ordered them a certain way to be most intuitive to read. However, this results in the table I want to update not being the one I start with, and I am having trouble updating it.
A dummy example of what I'd like to do is something like:
UPDATE b
FROM tableA a
JOIN tableB b
ON a.a_id = b.a_id
JOIN tableC c
ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
AND c.val > 10;
There are many posts about updating with joins here however they always have table being updated first. I know this is possible in SQL Server and hopefully its possible in MySQL Too!
The multi-table UPDATE syntax in MySQL is different from Microsoft SQL Server. You don't need to say which table(s) you're updating, that's implicit in your SET clause.
UPDATE tableA a
JOIN tableB b
ON a.a_id = b.a_id
JOIN tableC c
ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
AND c.val > 10;
There is no FROM clause in MySQL's syntax.
UPDATE with JOIN is not standard SQL, and both MySQL and Microsoft SQL Server have implemented their own ideas as an extension to standard syntax.
You have the ordering of the statements wrong. You can read up on the syntax here (I know, it's pretty hard to read.
UPDATE tableA a
JOIN tableB b
ON a.a_id = b.a_id
JOIN tableC c
ON b.b_id = c.b_id
SET b.val = a.val+c.val
WHERE a.val > 10
AND c.val > 10;
sql fiddle
This link should give you the syntax that MySQL needs and here is an example. Why do you need to join the two tables? is it to limit the records updated? I am asking because you can also do something like the following:
update B set B.x=<value>
where
B.<value> is in(
select A.y
from A left outer join B on A.<value>=B.<value>
)
上一篇: 使用select语句更新多行
下一篇: MySql更新连接表