Can't specify target table 'cms_user_login_attempts' for update in FROM clause

Question

I am getting the above order for this snippet of code:

            $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);

Not sure why i am getting this error, can someone help?

This very basic error see the related ones http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause?rq=1, also first search for your problem then ask it — M Khalid Junaid, Jan 09 '15 at 10:59
@MKhalidJunaid I had found this but I am finding it difficult to apply this to my code. — PHP Learner, Jan 09 '15 at 11:24

score 1 · Accepted Answer · answered Jan 09 '15 at 11:42

1

You can write your query as

$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)";

answered Jan 09 '15 at 11:42

M Khalid Junaid

63,861
10
90
118

1

Hello, I literally just wrote something similar, I will tick this an answer because you took time out to do it, thank you. As-salamu alaykum. – PHP Learner Jan 09 '15 at 11:45
Actually, just noticed this is vastly different to mine, still works fine. Could you explain the code please? – PHP Learner Jan 09 '15 at 11:49
1

@PHPLearner this operation cannot be performed when you are updating a table and selecting from same table to update for this we can use joins/sub selects to overcome this problem and gives news aliases to table involved in query, in above query i am using cross join which returns your desired count and then i can update original table – M Khalid Junaid Jan 09 '15 at 12:02

Can't specify target table 'cms_user_login_attempts' for update in FROM clause

1 Answers1