Why Does MySQL Throw an \"Incorrect Usage of UPDATE and LIMIT\" Error When Updating Multiple Tables?

Incorrect Usage of UPDATE and LIMIT with Multiple Tables in MySQL

When updating multiple tables using a JOIN operation in MySQL, certain restrictions apply. One such restriction is the inability to use the LIMIT clause, as indicated by the error message "Incorrect usage of UPDATE and LIMIT."

How to Fix the Issue:

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."

Therefore, in your provided code snippet:

<code class="php">$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";</code>

To correct the issue, you must remove the LIMIT 1 clause. This will result in updates being applied to all matching rows in both the "users" and "contact_info" tables, as per the specified conditions.

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