mysql - Referencing column depending on Ids of another column in sequence -

i trying update (reference) column (oid) of 1 table oid of table's column condition.

example :

customer table : ------------------ cid  name   oid ------------------- 1  abc     null 2  abc     null 3  abc     null 4  xyz     null --------------------  order table -------------- oid  name -------------- 10   abc 11   abc 12   abc 13   xyz -------------- 

ouput should :

customer table : ------------------ cid  name   oid ------------------- 1    abc     10 2    abc     11 3    abc     12 4    xyz     13 -------------------- 

i have tried following

update customer c, order o    set c.oid = o.oid    c.name = o.name; ----------------------------- update customer inner join order on customer.name=order.name set customer.oid=order.oid customer.oid null; 

but customer table being updated follows

customer table : ------------------ cid  name   oid ------------------- 1    abc     10 2    abc     10 3    abc     10 4    xyz     13 -------------------- 

the idea assign row number each of entries in customer table , order table.

thus when making inner join between these 2 tables have 2 conditions right (whereas 1 i.e. name).

one condition name , 1 row_number

you can go query:

update customer ct inner join (     select         customertable.cid,         ordertable.oid          (             select                 *,                  @rn1 := @rn1 + 1 row_number                             customer c             cross join (select @rn1 := 0) var             order cid         ) customertable     inner join (         select             *,              @rn2 := @rn2 + 1 row_number                     `order` o         cross join (select @rn2 := 0) var         order oid     ) ordertable on customertable. name = ordertable. name     , customertable.row_number = ordertable.row_number ) combinedtable on ct.cid = combinedtable.cid set ct.oid = combinedtable.oid 

note: since joining these 2 tables on matching name not sufficient looking for. that's why besides matching name assign row_number each of rows (both in customer , order table. make inner join between these 2 tables on matching name , row number. restricting 1 entry joined multiple times other entries table.

---

test schema & data:

_{couldn't add sql fiddle}

drop table if exists `customer`; create table `customer` (   `cid` int(11) not null auto_increment,   `name` varchar(100) not null,   `oid` int(11) default null,   primary key (`cid`) ); insert `customer` values ('1', 'abc', null); insert `customer` values ('2', 'abc', null); insert `customer` values ('3', 'abc', null); insert `customer` values ('4', 'xyz', null);  drop table if exists `order`; create table `order` (   `oid` int(11) not null,   `name` varchar(100) not null ); insert `order` values ('10', 'abc'); insert `order` values ('11', 'abc'); insert `order` values ('12', 'abc'); insert `order` values ('13', 'xyz'); 

see now, how customer table like:

select * customer; 

output:

cid name  oid 1   abc   10 2   abc   11 3   abc   12 4   xyz   13