MySQL Error: Incorrect usage of UPDATE and LIMIT

Question

How can I correct this problem so that my MySQL code works correctly.

Here is my MySQL code that gives me the problem.

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

Answer 1

As per the MySQL docs 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.

Answer 2

**if you want to update multiple rows using limit in mysql...directly limit you cant use try like this**

UPDATE table_name SET name='test'
     WHERE id IN (
         SELECT id FROM (
             SELECT id FROM table_name 
             ORDER BY id ASC  
             LIMIT 0, 10
         ) tmp
     );

Answer 3

I know it is an old question but it is the first link when googling this error. There is a workaround to solve this problem without performance issue (depending on your indexes) by using a derived table.

UPDATE table1 t1
JOIN (SELECT t1.id
    FROM table1 t1
    JOIN table2 t2 ON t1.id = t2.id
        AND t2.some_criteria = 'some_value'
    WHERE t1.other_criteria = 'other_value'
    LIMIT 10000
) tmp ON tmp.id = t1.id
SET t1.field_to_update = 'new_value'

Because the LIMIT is inside the subquery, the join will match only the number of rows of the clause LIMIT. So the query will update only those rows.

Answer 4

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

Answer 5

@Marc B provides the reason, why update normally can't work with limit.

And @Roopchand also provide a solution.

For people like me, who is trying to avoid turning off the safe update mode

https://stackoverflow.com/a/28316067/1278112
This answer is quite helpful. It give an example

UPDATE customers SET countryCode = 'USA' WHERE country = 'USA'; -- which gives the error, you just write:

UPDATE customers SET countryCode = 'USA' WHERE (country = 'USA' AND customerNumber <> 0); -- Because customerNumber is a primary key you got no error 1175 any more.

And when I face update with the multiple-table syntax, it also worked.

What I want but would raise error code 1175.

UPDATE table1 t1
        INNER JOIN
    table2 t2 ON t1.name = t2.name 
SET 
    t1.column = t2.column
WHERE
    t1.name = t2.name;

The working edition

UPDATE table1 t1
        INNER JOIN
    table2 t2 ON t1.name = t2.name 
SET 
    t1.column = t2.column
WHERE
    (t1.name = t2.name and t1.prime_key !=0);

Which is really simple and elegant. Since the original answer doesn't get too much attention (votes), I post more explanation. Hope this can help others.