Mysql current version not supporting 'LIMIT & IN/ALL/ANY/SOME subquery'

Question

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

o_id    host              b_id
9205    host1.abc.com     null
9206    host2.abc.com     null
9207    host3.abc.com     null

---more than 1000 rows

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

id      hostname              o_id   ip
18356   host1                 null   10.10.10.10
18357   host2                 null   10.11.11.11
18358   host3                 null   10.12.12.12

---more than 1000 rows

Now, if hostname(excluding domain name) matches in both tables and ip address in range ('10.10|10.11'), then I want to update both tables such that table2.o_id = table1.o_id and table1.b_id = table2.id

update table1 T1
inner join table2 T2 on T2.hostname = substring_index(T1.host, '.', 1)
set T2.o_id = T1.o_id , 
    T1.b_id = T2.id
where T1.b_id IS NULL AND
 T2.ip IN (select ip from table2 where ip regexp ('10.10|10.11')
limit 10); 

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.

Here, I am getting an error

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I am using MYSQL Versin 6.0

Answer 1

Just do an additional join instead of in:

update table1 T1 inner join
       table2 T2 
       on T2.hostname = substring_index(T1.host, '.', 1) join
       (select distinct ip
        from table2
        where ip regexp ('10.10|10.11')
        limit 10
       ) t3
       on t2.ip = t3.ip
set T2.o_id = T1.o_id , 
    T1.b_id = T2.id
where T1.b_id IS NULL ;