How to fix update +1 in a function?

Question

MySQL

here is the function

CREATE DEFINER=`root`@`%` FUNCTION `GetMailAcc`(`in_domen_id` int) RETURNS int(11)
BEGIN

DECLARE result_str, count_str INT(11);

  SET count_str = (
      SELECT COUNT(1) AS QTY  
      FROM L_mail_account AS MA 
      WHERE MA.domen_id = in_domen_id 
      ORDER BY last_upd DESC 
      LIMIT 1
    );

  IF count_str > 0 THEN
    SET result_str = (SELECT MA.id FROM L_mail_account AS MA WHERE MA.domen_id = in_domen_id LIMIT 1);
    UPDATE L_mail_account SET count_send = 1 + count_send WHERE id = result_str;
  ELSE
    SET result_str = (SELECT id FROM L_mail_account ORDER BY last_upd DESC LIMIT 1);
    UPDATE L_mail_account SET count_send= 1 + count_send WHERE id = result_str;
  END IF;


  RETURN result_str;
END

when you do like this SELECT GetMailAcc(99); - then there is no problem the counter( count_send ) increases by +1

and if the same request is used in the request, then an error occurs

SELECT
  MN.id,
  MN.m_fio,
  CONCAT( MN.m_name, '@', DN.d_name ) AS mail_name,
  MA.login_smtp,
  MA.pasw_smpt,
  MA.ssl_smtp,
  MA.server_smtp,
  MA.port_smtp 
FROM
  A_mail_name MN
  LEFT JOIN L_domens_name DN ON DN.id = MN.domen_id
  LEFT JOIN L_mail_account MA ON MA.id = GetMailAcc ( MN.domen_id ) 
WHERE
  MN.server_take = 99 
  AND MN.status_send = 0

then an error occurs

> Can't update table 'l_mail_account' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Answer 1

return the value of the function to a variable, and use the variable in the query.