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

Question

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             |
+------+----------------+

Possible duplicate of [mysql update column with value from another table](http://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table) — Jens, Jun 07 '16 at 06:46

score 3 · Accepted Answer · answered Jun 07 '16 at 06:46

3

Try Update with join:

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

answered Jun 07 '16 at 06:46

sagi

40,026
6
59
84

score 1 · Answer 2 · answered Jun 07 '16 at 06:45

1

Try this,

update tbl2
set tbl2.transaction_id = tbl1.id_trans 
from tbl1 inner join tbl2
on tbl1.id = tbl2.transaction_id
where tbl2.transaction_id = 53;

answered Jun 07 '16 at 06:45

Muhammad Abdul Arif Sarker

1,211
4
15
25

Tnx. But I don't want just tbl1.id = 53! My tables have much data – Karim Pazoki Jun 07 '16 at 06:56
@pk19000 it's up to you whether you need to use where clause or not. – Muhammad Abdul Arif Sarker Jun 07 '16 at 07:41
1

Thank you for your answer :) – Karim Pazoki Jun 07 '16 at 08:14

aarju mishra · Answer 3 · 2016-06-07T07:01:26.620

1

You can use this query:

    UPDATE tbl2
INNER JOIN tbl1 ON tbl1.id = tbl2.transaction_id
SET
    tbl2.transaction_id = tbl1.id_trans

edited Jun 07 '16 at 07:01

answered Jun 07 '16 at 06:52

aarju mishra

710
3
10

Tnx. But I don't want just `tbl1.id = 53`! My tables have much data. – Karim Pazoki Jun 07 '16 at 06:56
You want the transaction_id of tbl2 to be replaced with the id_trans of tbl1 or replaced with the id of tbl1?In this case it is replaced with the id_trans – aarju mishra Jun 07 '16 at 07:03
Tnx for yuor answer. – Karim Pazoki Jun 07 '16 at 07:07

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

3 Answers3