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

Understanding the MySQL Error: Incorrect Usage of UPDATE and LIMIT

When using MySQL's UPDATE statement to modify data, you may encounter the error "Incorrect usage of UPDATE and LIMIT." This error arises when you attempt to combine the UPDATE statement with either an ORDER BY or LIMIT clause within a multiple-table update operation.

Context:

In the code snippet provided, the UPDATE statement attempts to modify the users table and its related contact_info table based on specific conditions. However, it also includes a LIMIT 1 clause, which is not allowed in such scenarios.

The Solution:

The MySQL documentation explicitly states that for multiple-table UPDATE operations, ORDER BY and LIMIT cannot be used. This is because the UPDATE statement needs to update all matching rows in the joined tables, and limiting the update to a specific number of rows can lead to unexpected results.

To resolve this error, simply remove the LIMIT 1 clause from your UPDATE statement. The updated code will look something like this:

$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) . "')";
$r = mysqli_query ($mysqli, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($mysqli));

This modified code will perform the update operation as intended, without any restrictions on the number of matching rows.

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