CODEWITHSUNDEEP
mysqlsql
Karim Pazoki
Karim Pazoki

Reputation: 969

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

Upvotes: 3

Views: 1575

Answers (3)

aarju mishra
aarju mishra

Reputation: 710

You can use this query:

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

Upvotes: 1

sagi
sagi

Reputation: 40481

Try Update with join:

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

Upvotes: 3

Muhammad Abdul Arif Sarker
Muhammad Abdul Arif Sarker

Reputation: 1219

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;

Upvotes: 1

Related Questions