MySQL update a joined table

Question

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!

You need to put the `SET b.val = a.val+c.val` before `FROM ...`. See http://stackoverflow.com/questions/2334712/sql-server-update-from-select — Gerben, Nov 30 '11 at 19:29
This example has SET after the from, and that examples updates the first table in the join order. I would like to update a table in the middle of the join order. — Aaron Silverman, Nov 30 '11 at 19:36
That shouldn't matter. Even if it did you could just change the order of the joins (`tableB b JOIN tableA a ON a.a_id = b.a_id`). — Gerben, Nov 30 '11 at 19:53

Bill Karwin · Accepted Answer · 2018-02-23T17:24:43.237

136

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.

edited Feb 23 '18 at 17:24

answered Nov 30 '11 at 19:39

Bill Karwin

538,548
86
673
828

ORDER BY and LIMIt not use in join's – zloctb Jun 13 '15 at 16:21
@zloctb, yes, that's a good tip! For some reason, MySQL's multi-table UPDATE syntax does not support ORDER BY and LIMIT. Cf. http://dev.mysql.com/doc/refman/5.6/en/update.html – Bill Karwin Jun 13 '15 at 21:31
2

This was the only answer I came across in a lot of searching that gave a clear answer and explained how and why it is different from other SQL languages. – Case Silva Mar 21 '19 at 15:59

Andreas Wederbrand · Answer 2 · 2014-10-13T17:01:48.387

4

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

edited Oct 13 '14 at 17:01

answered Nov 30 '11 at 19:37

Andreas Wederbrand

38,065
11
68
78

1

That syntax doesn't work. You'd have to delete the FROM part and move the SET part to after the JOINs. – draca Oct 13 '14 at 14:28

score 3 · Answer 3 · edited Feb 23 '21 at 17:10

Another correct construction, which we can use in this situation:

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

The above example is take from: MySQL UPDATE JOIN.

Reaching for the MySQL 8.0 Reference Manual we will find such a description of multiple-table UPDATE syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET assignment_list
[WHERE where_condition]

The table_references clause lists the tables involved in the join.

So multiple-table MySQL's syntax doesn't support FROM, ORDER BY or LIMIT clauses as opposed to single-table syntax.

Thank you, @DavidOliver, for fixing my answer. – simhumileco Feb 23 '21 at 18:10 — simhumileco, Feb 23 '21 at 18:10

score 1 · Answer 4 · answered Nov 30 '11 at 19:34

1

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

answered Nov 30 '11 at 19:34

John Kane

4,383
1
24
42

That examples updates the first table in the join order. I would like to update a table in the middle of the join order. – Aaron Silverman Nov 30 '11 at 19:36
I know its been LONG, however, you could pick a column in any table. If you just have to update one table. – DGoiko Jun 03 '19 at 20:07

MySQL update a joined table

4 Answers4

Linked

Related