## Why is LIMIT Clause Not Allowed in Multi-Table UPDATE Queries in MySQL?

MySQL Error: Incorrect Usage of UPDATE and LIMIT

When executing an UPDATE query on multiple tables in MySQL, an error may occur due to incorrect usage of UPDATE and LIMIT. This issue arises when attempting to update records in multiple tables where the ON condition joins the tables and the LIMIT clause is used.

According to the MySQL documentation for UPDATE, "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 provided MySQL code, the query attempts to update rows in the "users" and "contact_info" tables based on a join condition. However, the LIMIT clause is used to limit the number of updated rows to 1. This is not permitted in multi-table UPDATE queries.

To resolve this issue, remove the LIMIT clause from the query. This will allow the UPDATE statement to affect all rows in the joined tables that meet the specified conditions. The modified code would look 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));

By removing the LIMIT clause, the query will update all rows in both the "users" and "contact_info" tables where the email address and active status match the specified values.

The above is the detailed content of ## Why is LIMIT Clause Not Allowed in Multi-Table UPDATE Queries in MySQL?. For more information, please follow other related articles on the PHP Chinese website!