sql - MYSQL Update Based On A Row Index Equaling An ID In Another Table -

i have 2 tables. i'd update row in table1 based on same row index in table2. ids don't match but id in table 2 matches row index. there more data in table 2, don't care if rows missed.

how achieve in mysql update statement?

   table 1      ______________      table 2     _____________    row number  |  id  | value |    row number | id  | value |                |--------------|               |-----|-------|         1      |  2   |     |        1      |  1  |   w   |         2      |  4   |   b   |        2      |  2  |   x   |         3      |  6   |   c   |        3      |  3  |   y   |                                        4      |  4  |   z   |    to:     table 1      ______________         row number |  id  | value |                   |--------------|            1      |  2   |   w   |            2      |  4   |   x   |           3      |  6   |   y   |     

this works, it's not pretty.

set @c=0; update t1 join (     select tx.id,t2.value     t2     join (         select @c:=@c+1 rownum, value, id         (             select * t1  order id         ) t3     ) tx     on t2.id=tx.rownum) tupdate on t1.id = tupdate.id set t1.value=tupdate.value; 

the basic point behind use variable count rows, use join. ended using multiple nested selects because requirement update t1 after making version of rows counted.

the set @ start required stop overcounting if repeated. see mysql - row number on select inspiration.