UPDATE Inner Join with limit?

Question

I wanted to update up to 8000 rows with INNER JOIN.

I get an error:

Incorrect usage of UPDATE and LIMIT

It does work if I don't include the INNER JOIN

UPDATE `loop`
INNER JOIN data ON data.number = loop.number
SET robot = 1
WHERE `done` != 1 AND `robot` = 0
limit 8000

How do I get this to work? Example SQL would be great.

Possible duplicate http://stackoverflow.com/questions/9080403/update-with-order-by-and-limit-not-working-in-mysql. Apparently limit can only be used with SELECT statements — Nick Patsaris, Jun 14 '14 at 07:25
Consider providing proper DDLs (and/or an sqlfiddle) TOGETHER WITH THE DESIRED RESULT SET — Strawberry, Jun 14 '14 at 13:21

score 0 · Answer 1 · answered Jun 14 '14 at 08:47

I assume your table has a primary key which I will call number here. Then copy the first 8000 numbers in a temporary table and join your update to that table:

create temporary table if not exists PKs (number integer);
insert into PKs select number from loop where done != 1 and robot = 0 limit 8000;
update data inner join PKs on data.number = PKs.number set data.robot = 1;
drop table PKs;

Maybe my usage of the done and robot field is not correct, but from your statement it's not clear to which table they belong.

UPDATE Inner Join with limit?

1 Answers1