Pk19000 Pk19000 - 5 months ago 7
SQL Question

update tables value from another table in mysql where updated field is foreign key

I have to table like this. Now

tbl2.transaction_id
is from
tbl1.id
. I wanna to update
tbl2.transaction_id
with
tbl1.id_trans
values that
transaction_id
now pointed to them. Can I do this with just one query? Thanks.

tbl1
+------+----------------+
| id | id_trans |
+------+----------------+
| 53 | 531 |
| 54 | 532 |
| 55 | 533 |
| 56 | 554 |
| 57 | 555 |
+------+----------------+


tbl2
+------+----------------+
| id | transaction_id |
+------+----------------+
| 10 | 53 |
| 11 | 55 |
| 12 | 56 |
+------+----------------+

Answer

Try Update with join:

UPDATE tbl2 t
INNER JOIN tbl1 s
 ON(t.transaction_id = s.id)
set t.transaction_id = s.id_trans
Comments