Update data from second table by other tables

Question

I have 3 tables and I want to update the first table from second table by third table and fourth table.

The IDs in table1 & table2 are unique and the IDs in table3 & table4 are unique. In table2 & table3 uid is unique but I need to check another value too (source).

table1

| ID       | value          |
| -------- | -------------- |
| 1        | apple          |
| 2        | banana         |

table2

| ID       | uid            | source         |
| -------- | -------------- | -------------- |
| 1        | 10             | tableA         |
| 2        | 11             | tableA         |
| 3        | 10             | tableB         |
| 4        | 11             | tableB         |

table3

| ID       | uid            | source         |
| -------- | -------------- | -------------- |
| 5        | 10             | tableA         |
| 6        | 11             | tableA         |
| 7        | 10             | tableB         |
| 8        | 11             | tableB         |

table4

| ID       | value          |
| -------- | -------------- |
| 5        | aaa            |
| 6        | bbb            |

I tried to run this query:

UPDATE table1 t1 
INNER JOIN table2 t2 ON t2.ID = t1.ID AND t2.source = 'tableA' 
INNER JOIN table3 t3 ON t3.source = 'tableA' AND t3.uid = t2.uid 
INNER JOIN table4 t4 ON t4.ID = t3.ID SET t1.value = t4.value;

But I get error: #1205 - Lock wait timeout exceeded; try restarting transaction

What I wrote incorrect?

Answer 1

You can join all 3 tables in the UPDATE statement:

UPDATE table1 t1
INNER JOIN table2 t2 ON t2.ID = t1.ID
INNER JOIN table3 t3 ON t3.uid = t2.uid
SET t1.value = t3.value;