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));
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.
**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
);
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.
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
@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.
