Conditional load using data file in mysql

Question

I am trying to load a file with a condition using mysql

GIFTCARDS (table):
    id| store    | amount   | 
    1 | starbucks|          | 
    2 | target   |          | 
    3 | starbucks|          | 
    4 | target   |          | 
    5 | target   |          | 
    6 | target   |          | 

winning.txt (file)
25
10
15

I need to assign "amount" randomly. Please note that there are 4 rows with "target" but winning file contains 3 rows. This means there will be one "target" row, selected randomly, that will have amount null. Amount for each Starbucks card will be loaded using a separate file.

Is it possible to do so using a mysql query?

Answer 1

Load winning.txt into winning (a table) using load data infile.

You can then do the rest in a MySQL query. The key is to enumerate the rows and do a join:

update giftcards gc join
       (select @rn := @rn + 1 as x, id
        from giftcards cross join
             (@rn := 0) vars
        where store = 'target'
       ) gcx
       on gc.id = gcx.id join
       (select @rnw := @rnw + 1) as x, amount
        from winning cross join
             (select @rnw := 0) vars
       ) w
       on gcx.x = w.x
    set gc.amount = w.amount;