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

Question

I have two tables. I'd like to update a row in table1 based on the same row index in table2. The IDs don't match but but the ID in table 2 matches the row index. There will always be more data in table 2, but I don't care if the extra rows are missed.

How would I achieve this in a mysql UPDATE statement?

   table 1      ______________      table 2     _____________
   Row number  |  id  | value |    Row number | id  | value |
               |--------------|               |-----|-------|
        1      |  2   |   A   |        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   |

The row number in the table. I.e. the first row row[0], or the second row row[1]. — big_tommy_7bb, Sep 07 '16 at 11:31
@1000111 Clearly not. Otherwise the result would be W X Y ;-) — Strawberry, Sep 07 '16 at 11:31
@tim_barber_7BB Rows in RDBMS tables represent unordered sets. So 'row index' or 'row number' has no meaning — Strawberry, Sep 07 '16 at 11:32
Got it after having a close look @Strawberry. Your expected output doesn't make sense -- to the PO. — 1000111, Sep 07 '16 at 11:35
You can use left or right joins. Firstly, its better to use / set foreign keys otherwise it tends to become confusing. Secondly, you can use the select statement for fetching multiple values of one table with id of another table. — Abhrapratim Nag, Sep 07 '16 at 11:51
The expected answer makes sense: the requirement is for `table 1` to be updated where its ordinal row number matched the id value in `table 2`. The ordinal row number is found by counting rows when sorted by `id asc`. The row numbers for the other tables aren't required. — Chris Lear, Sep 07 '16 at 11:55
I take that back. The expected answer doesn't make sense... the output should be W,X,Y, not X,Y,Z (but the numbering in the question starting at 1 doesn't match the comment which suggests starting at 0) — Chris Lear, Sep 07 '16 at 12:04

score 0 · Accepted Answer · edited May 23 '17 at 12:16

This works, but it's not pretty.

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

The basic point behind it is to use a variable to count rows, then use that to join. I ended up using multiple nested selects because the requirement is to update t1 but only after making a version of it with the rows counted.

The set at the start is required to stop it from overcounting if repeated. See MySQL - Get row number on select for the inspiration.

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

1 Answers1