subquery in where clause of UPDATE statement

Question

I have the database of ATM card in which there are fields account_no,card_no,is_blocked,is_activated,issue_date Fields account number and card numbers are not unique as old card will be expired and marked as is_block=Y and another record with same card number ,account number will be inserted into new row with is_blocked=N . Now i need to update is_blocked/is_activated with help of issue_date i.e

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 is doesn't allow me to do so it throws 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

That's one of those stupid limitations of the MySQL parser. The usual way to solve this is to use a JOIN query as Mahmoud has shown.

The (at least to me) surprising part is that it really seems a parser problem, not a problem of the engine itself because if you wrap the sub-select into a derived table, this does work:

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
                    )