Search code examples
mysqlsql-updatetemp-tables

MySQL UPDATE table columns based on values in other tables

I have to update column values in a table based on values in other tables. This is what i have:

 TABLE_A
|----------------|
|    id   |  val  |
|----------------|
|    1    |   a   |
|    2    |   b   |
|    3    |   c   |
|    4    |   a   |
|    5    |   b   |
|    6    |   c   |
|----------------|


 TEMPORARY TABLE_B
|----------------|
|    id   |  val  |
|----------------|
|    4    |   a   |
|    5    |   b   |
|    6    |   c   |
|----------------|


 TEMPORARY TABLE_C
|----------------------------|
|    id   |  val1  |   val2  |
|----------------------------|
|    h    |    4    |     b     |
|    k    |    4    |     e     |
|    n    |    5    |     a     |
|    o    |    6    |     c     |
|    p    |    6    |     d     |
|    q    |    5    |     g     |
|----------------------------|


What I want to do is:

For each row in table_a where (table_a.id = table_b.id) then
     where (table_a.val = table_c.val2) set table_a.val = table_c.id

I cannot seem to write the correct UPDATE statement for this.

Can anyone help?

Solution

  • I believe something along these lines will work for you (using INNER JOIN)

    UPDATE table_a A
    INNER JOIN table_b B ON A.id = B.id
    INNER JOIN table_c C ON A.val = C.val2
    SET A.val = C.id