MySQL: Updating id value which is PK in another table

Question

I have a table table1 which has columns 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 which has columns as PK, ipaddress, b_id, env

id      ip              o_id  env
18356   10.10.10.10     null  dev
18357   10.10.10.11     null  prod
18358   10.10.10.12     null  qa

---more than 1000 rows

Now, if ipaddress matches in both tables and table2.env IN ('dev', 'qa'), then I want to update both tables such that table2.o_id = table1.o_id and table1.b_id = table2.id

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 written below query.

update table1 t1
inner join table2 t2 on t1.ip = t2.ip 
set t1.b_id = t2.id,
     t2.o_id = t1.o_id
where t2.env IN ('dev', 'qa')
limit 2

I am getting an error incorrect usage of limit.

I want to update only 2 rows first and see how it looks? I dont want to update the whole table all at once

And what's the problem with your query? (Apart from the fact that limit is pretty much meaningless without an order by) — Shadow, Jan 01 '18 at 22:10
I really-really wonder why people cannot search for a specific error message... — Shadow, Jan 01 '18 at 22:35

Gordon Linoff · Answer 1 · 2018-01-02T01:50:43.940

0

You can't do exactly what you want. MySQL does not allow limit with a join. You can use a subquery to restrict the number of rows:

update table1 t1 inner join
       table2 t2
       on t1.ip = t2.ip join
       (select ip
        from table1 t1
        limit 2
       ) t1ip
       on t1.ip = t1ip.ip
    set t1.b_id = t2.id,
        t2.o_id = t1.o_id
where t2.env IN ('dev', 'qa')

edited Jan 02 '18 at 01:50

answered Jan 01 '18 at 22:22

Gordon Linoff

1,242,037
58
646
786

I am getting an error `table db1.t1 doesn't exist` – meallhour Jan 01 '18 at 22:57

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

0

You can't use Limit directly in the update queries. You should try something like that;

update table1 set col1 = '' where b_id IN (
select t1.b_id table1 t1 from
inner join table2 t2 on t1.ip = t2.ip 
set t1.b_id = t2.id,
     t2.o_id = t1.o_id
where t2.env IN ('dev', 'qa')
limit 2)

answered Jan 01 '18 at 22:22

lucky

12,734
4
24
46

Yes, you can use limit in update queries, just not with the multiple table syntax. – Shadow Jan 01 '18 at 22:34
Thanks but I am not using `col1` anywhere. – meallhour Jan 01 '18 at 22:57

MySQL: Updating id value which is PK in another table

2 Answers2