MySQL update with two subqueries

Question

I'm trying to update one column of MySQL table with subquery that returns a date, and another subquery for the WHERE clause.

Here is it:

UPDATE wtk_recur_subs_temp 
   SET wtk_recur_date = (SELECT final_bb.date
                           FROM final_bb, wtk_recur_subs 
                          WHERE final_bb.msisdn = wtk_recur_subs.wtk_recur_msisdn) 
WHERE wtk_recur_subs_temp.wtk_recur_msisdn IN (select final_bb.msisdn 
                                                 from final_bb)

The response from the MySQL engine is "Subquery returns more than 1 row".

Answer 1

Use:

UPDATE wtk_recur_subs_temp,
       final_bb, 
       wtk_recur_subs 
   SET wtk_recur_subs_temp.wtk_recur_date = final_bb.date
 WHERE final_bb.msisdn = wtk_recur_subs.wtk_recur_msisdn
   AND wtk_recur_subs_temp.wtk_recur_msisdn = final_bb.msisdn

The error is because:

SET wtk_recur_date = (SELECT final_bb.date
                        FROM final_bb, wtk_recur_subs 
                       WHERE final_bb.msisdn = wtk_recur_subs.wtk_recur_msisdn) 

...the final_bb.date value is all the date values where the final_bb and wtk_recur_subs msisdn column values match.

Answer 2

This may come as an utter shock to you, but one of your subqueries is returning more than one row!

This isn't permitted in the circumstance you've set up. Each of those two subqueries must return one and only one row. Or no rows.

Perform each subquery on it's own and determine which one is returning more than one row. If they shouldn't return more than one row, your data may be wrong. If they should return more than one row, you'll either want to modify the data so they don't (as I assume you expect), or add a LIMIT clause. Or add an aggregate function (like MAX) outside the query to do something proper with the multiple rows being returned.