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

Spalding

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. – Recoil

UPDATE t1 JOIN t2 ON t1.id = t2.t1_id JOIN t3 ON t3.id = t2.t3_id SET t1.col = 'newval' – Recoil

The mentioned question is here: #15207246 – Hoang

Answer 1

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]

Soupy

Weird thing is however that my HeidiSQL software reports zero affected rows, although the data shows the updates were done. – Abdominous

@Abdominous 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 – Jew

If you need a visual aid to get your joins correct: browse-tutorials.com/tutorial/mysql-joins-visual-representation – Hunkydory

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

Where is WHERE?? Or WHERE is not possible? – Rame

@Rame WHERE its almost like ON here . ON is a condition . – Soupy

@green WHERE comes last, after SET. echo_Me please add WHERE in your post, because later comments aren't shown. – Travax

Answer 2

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

Acroterion

I tried this on 5.5.62 and mysql didin't like the syntax. According to the manual [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 – Studnia

This does an implicit JOIN in the same way doing SELECT * FROM TABLE_A, TABLE_B ... does – Goodnatured

So does that mean that in 5.5 only implicit joins form is accepted for update? – Saltine

@Saltine 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." – Hautboy

Not the exact same result -- you can do left joins with the join syntax. – Pierrette

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. – Kemeny

Answer 3

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

Viquelia

Welcome to Stack Overflow! Thank you for this code snippet, which may provide some immediate help. A proper explanation would greatly improve 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. – Massie

Answer 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";

Annadiana

Answer 5

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;

Gantlet

Answer 6

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;

Westing

Answer 7

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

Husain