Why Does MySQL Throw \'Incorrect Usage of UPDATE and LIMIT\' Error?

Incorrect Usage of UPDATE and LIMIT

MySQL users may encounter the error "Incorrect usage of UPDATE and LIMIT" when attempting to execute queries involving both the UPDATE statement and the LIMIT clause.

To resolve this issue, it's important to understand the limitations of the UPDATE statement when used with multiple tables. According to the MySQL documentation, "For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used."

In the example code provided:

$q = "UPDATE users INNER JOIN contact_info ON contact_info.user_id = users.user_id SET active.users = NULL WHERE (email.contact_info = '" . mysqli_real_escape_string($mysqli, $x) . "' AND active.users = '" . mysqli_real_escape_string($mysqli, $y) . "') LIMIT 1";

The query attempts to perform an update using multiple tables (users and contact_info) while also applying a LIMIT clause. As explained earlier, LIMIT cannot be used in such scenarios.

To rectify the issue, consider modifying the query as follows:

$q = "UPDATE users INNER JOIN contact_info ON contact_info.user_id = users.user_id SET active.users = NULL WHERE (email.contact_info = '" . mysqli_real_escape_string($mysqli, $x) . "' AND active.users = '" . mysqli_real_escape_string($mysqli, $y) . "');";

By removing the LIMIT clause, the query will successfully execute and update the specified row(s) in the users table.

The above is the detailed content of Why Does MySQL Throw \'Incorrect Usage of UPDATE and LIMIT\' Error?. For more information, please follow other related articles on the PHP Chinese website!