mysql - 如何摆脱SET？

我不想在查询中包含变量。我该怎么做？
下面是我的MYSQL查询：

SET @totalcnt:=(
    select prof.failed_attempt_count
    from profile prof, app_user usr
    where upper(usr.username) = upper("admin") and
          prof.id = usr.profile_id and
          usr.enterprise_id = (select id
                               from enterprise
                               where upper(enterprise_code) = upper("e100") ));

update app_user
set cur_failed_attempt_count = cur_failed_attempt_count + 1
where (upper(username) = upper("admin") and
      enterprise_id = (select id
                       from enterprise
                       where upper(enterprise_code) = upper("e100")) and
                             cur_failed_attempt_count < @totalcnt
);

基本上我想去掉@totalcnt命令，然后先删除SET命令。
请帮忙。

最佳答案

您是否尝试将@totalcnt表达式直接添加到主查询中？
编辑：包围限制1，因为它被SELECT搜索受影响的行数，然后是行数

  update app_user
  set cur_failed_attempt_count = cur_failed_attempt_count + 1
  where (upper(username) = upper("admin")
      and enterprise_id = (select id
                           from enterprise
                           where upper (enterprise_code) = upper("e100"))
      and cur_failed_attempt_count < (select prof.failed_attempt_count
                                      from profile prof, app_user usr
                                      where upper(usr.username) = upper("admin")
                                        and prof.id = usr.profile_id
      and usr.enterprise_id = (select id
                               from enterprise
                               where upper(enterprise_code) = upper("e100") LIMIT 1))
  );