How can I do three table JOINs in an UPDATE query?

Question

I asked a question and got this reply which helped.

   UPDATE TABLE_A a JOIN TABLE_B b
   ON a.join_col = b.join_col AND a.column_a = b.column_b
   SET a.column_c = a.column_c + 1

Now I am looking to do this if there are three tables involved something like this.

    UPDATE tableC c JOIN tableB b JOIN tableA a

My question is basically... is it possible to do three table joins on an UPDATE statement? And what is the correct syntax for it?

Do I do the following?

 JOIN tableB, tableA
 JOIN tableB JOIN tableA

Sure it is possible. Give it a try. The syntax is just like you have it -you just need to add the next `JOIN` and its `ON` condition, same as you would in a `SELECT` query. — Michael Berkowski, Mar 04 '13 at 19:27
`UPDATE t1 JOIN t2 ON t1.id = t2.t1_id JOIN t3 ON t3.id = t2.t3_id SET t1.col = 'newval'` — Michael Berkowski, Mar 04 '13 at 19:28
The mentioned question is here: http://stackoverflow.com/questions/15206746/match-2-sql-columns-if-then-update-a-different-column-by-1 — Urs, Nov 07 '13 at 10:21

score 862 · Accepted Answer · edited Aug 07 '21 at 22:00

862

The answer is yes, you can.

Try it like this:

UPDATE TABLE_A a
    JOIN TABLE_B b ON a.join_col = b.join_col AND a.column_a = b.column_b
    JOIN TABLE_C c ON [condition]
SET a.column_c = a.column_c + 1

For a general update join:

UPDATE TABLEA a
JOIN TABLEB b ON a.join_colA = b.join_colB
SET a.columnToUpdate = [something]

edited Aug 07 '21 at 22:00

Peter Mortensen

30,738
21
105
131

answered Mar 04 '13 at 19:28

echo_Me

37,078
5
58
78

4

Weird thing is however that my HeidiSQL software reports zero affected rows, although the data shows the updates were done. – Pianoman Feb 01 '16 at 08:06
1

@Pianoman To me it happened as well and it had something to do with ON UPDATE CURRENT_TIMESTAMP, I just added manually the update and it fixed it, just saying if it happens to anyone else – eric.itzhak Jun 06 '16 at 13:35
If you need a visual aid to get your joins correct: https://browse-tutorials.com/tutorial/mysql-joins-visual-representation – ram4nd Jan 27 '17 at 09:29
1

I think the following is a better General Plan: `UPDATE table A JOIN table B ON {join data} JOIN table C ON {join data} JOIN {more join tables} SET A.column = {expression}` (forgive me if this blasted editor won't let me enter newlines without doing a full post) – UncaAlby May 31 '17 at 17:07
Where is `WHERE`?? Or `WHERE` is not possible? – Green Sep 06 '17 at 11:38
@Green WHERE its almost like ON here . ON is a condition . – echo_Me Sep 17 '17 at 09:21
1

@green WHERE comes last, after SET. echo_Me please add WHERE in your post, because later comments aren't shown. – JPT Feb 20 '18 at 09:47

score 48 · Answer 2 · edited Aug 07 '21 at 22:01

48

An alternative way of achieving the same result is not to use the JOIN keyword at all.

UPDATE TABLE_A, TABLE_B
SET TABLE_A.column_c = TABLE_B.column_c + 1
WHERE TABLE_A.join_col = TABLE_B.join_col

edited Aug 07 '21 at 22:01

Peter Mortensen

30,738
21
105
131

answered Jun 17 '15 at 15:31

Matas Vaitkevicius

58,075
31
238
265

3

I tried this on 5.5.62 and mysql didin't like the syntax. According to the manual [https://dev.mysql.com/doc/refman/5.6/en/update.html], the query should be: `UPDATE TABLE_A, TABLE_B SET TABLE_A.column_c = TABLE_A.column_c +1 WHERE TABLE_A.join_col = TABLE_B.join_col ` – Noe Nieto Jun 23 '15 at 01:47
10

This does an implicit `JOIN` in the same way doing `SELECT * FROM TABLE_A, TABLE_B ...` does – Madbreaks Sep 22 '15 at 22:26
So does that mean that in 5.5 only implicit joins form is accepted for update? – userfuser Dec 28 '15 at 15:06
@userfuser No, it doesn't, the manual states the syntax: `UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ... [WHERE where_condition]` Later on, the manual states: "The `table_references` clause lists the tables involved in the join. Its syntax is described in [Section 13.2.9.2, JOIN Syntax](https://dev.mysql.com/doc/refman/5.6/en/join.html)." – hmundt Mar 02 '16 at 13:54
5

Not the exact same result -- you can do left joins with the join syntax. – Gerard ONeill Oct 18 '16 at 16:02
Thanks. This worked but how would you sort the second table(table_b), so that the latest value of column_c is saved in table_a column_c. – ziggrat Jun 20 '17 at 09:53

score 13 · Answer 3 · edited Aug 07 '21 at 22:03

13

Below is the update query which includes both JOIN and WHERE. In the same way, we can use multiple join/where clauses:

UPDATE opportunities_cstm oc JOIN opportunities o ON oc.id_c = o.id
 SET oc.forecast_stage_c = 'APX'
 WHERE o.deleted = 0
   AND o.sales_stage IN('ABC','PQR','XYZ')

edited Aug 07 '21 at 22:03

Peter Mortensen

30,738
21
105
131

answered Jun 19 '17 at 14:56

Nitin Shukla

131
1
2

3

Welcome to Stack Overflow! Thank you for this code snippet, which may provide some immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its educational value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with similar, but not identical, questions. Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Jun 19 '17 at 15:02

score 4 · Answer 4 · edited Aug 07 '21 at 22:02

4

An alternative general plan:

UPDATE table A
JOIN table B ON {join fields}
JOIN table C ON {join fields}
JOIN {as many tables as you need}
SET A.column = {expression}

Example:

UPDATE person P
JOIN address A ON P.home_address_id = A.id
JOIN city C ON A.city_id = C.id
SET P.home_zip = C.zipcode;

edited Aug 07 '21 at 22:02

Peter Mortensen

30,738
21
105
131

answered May 31 '17 at 17:18

UncaAlby

5,146
1
16
19

score 4 · Answer 5 · edited Apr 22 '23 at 10:10

4

Yes, you can do a three-table join for an update statement. Here is an example:

UPDATE customer_table c

  JOIN
      employee_table e
      ON c.city_id = e.city_id
  JOIN
      anyother_table a
      ON a.someID = e.someID

SET c.active = "Yes"
WHERE c.city = "New york";

edited Apr 22 '23 at 10:10

William Desportes

1,412
1
22
31

answered Jul 20 '20 at 12:46

vishwampandya

1,067
11
11

score 1 · Answer 6 · edited Aug 07 '21 at 22:04

1

For a PostgreSQL example:

UPDATE TableA AS a
SET param_from_table_a=FALSE -- param FROM TableA
FROM TableB AS b
WHERE b.id=a.param_id AND a.amount <> 0;

edited Aug 07 '21 at 22:04

Peter Mortensen

30,738
21
105
131

answered Dec 28 '17 at 08:24

Mс1er

59
5

score 0 · Answer 7 · answered Jan 01 '22 at 10:53

0

none of answer does not work for me I find this on mysql manual

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

answered Jan 01 '22 at 10:53

arman amirkamali

81
4

How can I do three table JOINs in an UPDATE query?

7 Answers7

Linked

Related