Subquery in where the UPDATE statement clause

Question

I have an ATM card database in which there are account_no, card_no, is_blocked, is_activated, issue_date fields. The field account number and card numbers are not unique as the obsolete card expires and is marked as is_block = Y and another record with such same card number, the account number will be inserted in a new line with is_blocked = N. Now I need to update is_blocked / is_activated using issue_date ie

UPDATE card_info set is_blocked='Y' where card_no='6396163270002509' AND opening_date=(SELECT MAX(opening_date) FROM card_info WHERE card_no='6396163270002509') 

but this does not allow me to do this, it gives the following error

 1093 - You can't specify target table 'card_info' for update in FROM clause 

Answer 1

Try this instead:

 UPDATE card_info ci INNER JOIN ( SELECT card_no, MAX(opening_date) MaxOpeningDate FROM card_info GROUP BY card_no ) cm ON ci.card_no = cm.card_no AND ci.opening_date = cm.MaxOpeningDate SET ci.is_blocked='Y' WHERE ci.card_no = '6396163270002509' 

Answer 2

This is one of these silly MySQL parser limitations. The usual way to solve this is to use a JOIN request, as Mahmoud has shown.

The amazing part (at least for me) is that it really seems to be the problem of the parser, and not the problem of the engine itself, because if you complete the subselection in the view, this works:

 UPDATE card_info SET is_blocked='Y' WHERE card_no = '6396163270002509' AND opening_date = ( select max_date from ( SELECT MAX(opening_date) as_max_date FROM card_info WHERE card_no='6396163270002509') t )