Why Can\'t I Use LIMIT in an UPDATE Query with Multiple Tables in MySQL?

Incorrect Usage of UPDATE and LIMIT in MySQL Queries

Question:

How can I resolve the error "Incorrect usage of UPDATE and LIMIT" when executing a MySQL query?

Problem:

The following MySQL code raises an error due to incorrect use of UPDATE and LIMIT:

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

Answer:

The MySQL documentation for UPDATE states:

"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, when using the multiple-table syntax for UPDATE, it is not possible to use the LIMIT clause. This restriction is in place to prevent inconsistent updates across multiple tables.

Correction:

To resolve the error, remove the LIMIT clause from the query:

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

The above is the detailed content of Why Can\'t I Use LIMIT in an UPDATE Query with Multiple Tables in MySQL?. For more information, please follow other related articles on the PHP Chinese website!