MySQL: Updating id value in one table from another table

Question

I have a table table1 o_id as PK, ipaddress, b_id

o_id    ip              b_id
9205    10.10.10.10     null
9206    10.10.10.11     null
9207    10.10.10.12     null

---more than 1000 rows

I have another table table2 id as PK, ipaddress, b_id

id      ip              o_id
18356   10.10.10.10     null
18357   10.10.10.11     null
18358   10.10.10.12     null

---more than 1000 rows

Now, if ipaddress matches in both tables, then I want to update both tables such that table2.o_id = table1.o_id and table1.b_id = table2.id

update table1  
set b_id = table2.id
where ip = table2.ip

Here I want to update o_id in second table from the o_id in first table. I also want to update b_id in first table from id in second table.

Is above query correct? there a way to update both tables in single query?

Can i ask why you want such update after that you will have same data in 2 tables ? — M Khalid Junaid, Jan 01 '18 at 06:44
Are you asking how to do a JOIN in an UPDATE? I posted an example here: https://stackoverflow.com/questions/2114534/mysql-syntax-for-join-update/2114583#2114583 — Bill Karwin, Jan 01 '18 at 07:08

ScaisEdge · Accepted Answer · 2018-01-01T21:36:04.597

5

Looking too your request you could use update with join

update table1 t1
inner join table2 t2 on t1.ip = t2.ip 
set t1.o_id = t2.o_id, 
    t1.b_id = t2-id

but looking to your data you have table2.o_id = null so with the update you make all the o_id (primary key ) null .. and this is not possible (and no sense to me)

if you need update b_id in first table from id in second table then should be

update table2 t2
inner join table1 t1 on t1.ip = t2.ip 
set t2.o_id = t1.o_id, 
    t2.id = t1.b_id

edited Jan 01 '18 at 21:36

answered Jan 01 '18 at 07:58

ScaisEdge

131,976
10
91
107

Here I want to update `o_id` in second table from the `o_id` in first table. I also want to update `b_id` in first table from `id` in second table. I have also updated my question. Can you plz help? – meallhour Jan 01 '18 at 21:09

score 0 · Answer 2 · answered Jan 01 '18 at 08:20

You don't need an update at all! First to be able to correctly (and meaningfully) update ref IDs based on the IP address field that would require that IP address is unique in the both tables. So, if it is unique you can use it to join tables anyway, and get IDs from each tables. So no need to store it when you can get it by simply joining the tables.

MySQL: Updating id value in one table from another table

2 Answers2