无法指定目标表“cms_user_login_attempts”以便在FROM子句中进行更新

Question

我得到了以下代码片段的上述命令：

            $qry_WriteToDatabase = "    INSERT INTO cms_user_login_attempts
                                (
                                    cula_user_id,
                                    cula_date_time,
                                    cula_remote_host
                                )
                    VALUES      (
                                    " . $db->SQLString($row->user_id) . ",
                                    Now(),
                                    " . $db->SQLString($str_RemoteHost, true) . "
                                )";
            $db->query($qry_WriteToDatabase);

            $qry_UpdateCount = "UPDATE cms_user_login_attempts 
                                SET cula_attempt_count = (
                                    SELECT COUNT(*)
                                    FROM cms_user_login_attempts
                                    WHERE cula_user_id = ".$db->SQLString($row->user_id)."
                                    AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR))
                                    WHERE  cula_user_id = ".$db->SQLString($row->user_id)." 
                                    AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)";
            $db->query($qry_UpdateCount);

不知道为什么我会犯这个错误，有人能帮忙吗？

Answer 1

您可以将查询写成

$qry_UpdateCount = "UPDATE cms_user_login_attempts a
    CROSS JOIN (SELECT COUNT(*) user_count
    FROM cms_user_login_attempts
    WHERE cula_user_id = ".$db->SQLString($row->user_id)."
    AND cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
) b
SET a.cula_attempt_count = b.user_count
WHERE  a.cula_user_id = ".$db->SQLString($row->user_id)." 
AND a.cula_date_time > DATE_SUB(NOW(), INTERVAL 24 HOUR)";