CODEWITHSUNDEEP
mysqlsqlsql-update
Logan
Logan

Reputation: 1694

MySQL UPDATE multiple join not working

I am trying following update query with join, but it doesn't update matching row.

UPDATE table1 T1
INNER JOIN table2 T2 ON T2.id=10
INNER JOIN table3 T3 ON T3.category_id=T2.category_id
SET T1.value=T3.value
WHERE T1.id=T3.id

Is MySQL support update query with nested join?

Upvotes: 0

Views: 393

Answers (3)

Strawberry
Strawberry

Reputation: 33945

It would be more ususal to see that query written this way...

UPDATE table1 T1
  JOIN table3 T3 
    ON t3.id = t1.id
  JOIN table2 T2 
    ON T2.category_id = T3.category_id
   SET T1.value = T3.value
 WHERE T2.id=10;

Upvotes: 0

KevinLH
KevinLH

Reputation: 338

I am tempted to say your query should work. However, maybe MySQL does not like the fact the join condition on your second line has little to do with table1?

Does the following work?

UPDATE table1 T1
  INNER JOIN table3 T3 ON T3.id = T1.id
  INNER JOIN table2 T2 ON T2.category_id = T3.category_id
SET T1.value = T3.value
WHERE T2.id = 10

Upvotes: 1

Deepak Rai
Deepak Rai

Reputation: 2203

UPDATE table1 T1
SET T1.value=T3.value
INNER JOIN table2 T2 ON T2.id=10
INNER JOIN table3 T3 ON T3.category_id=T2.category_id
WHERE T1.id=T3.id

Upvotes: 2

Related Questions