How do I Update Only a Few Rows at a Time? (Multiple-Table Syntax)

Question

Is there a way without using a script to run an UPDATE that won't slow the DB by only doing a certain amount of rows at a time?

I'm performing

UPDATE .. SET .. FROM .. INNER JOIN .. ON ... WHERE

and it's going to update 3,171 rows in a very large table.

I don't want to lock the DB or slow it down. Any ideas?

NOTE:

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.

I was performing an INNER JOIN and LIMIT was not allowed to be used, using LIMIT as a solution does not work in this case.

Answer 1

Believe it or not, you can use LIMIT!

UPDATE mytable SET ... WHERE ... LIMIT 10;

I know it sounds weird, and it is non-deterministic, but it's so handy!

Edit

However, for updating joins, limit is not supported. However, there is an solution!

Use variable to control how many rows are updated. Here's a general look at how it works:

set @i := 0;
update table1 t1
join table2 t2 on t1.keycol = t2.keycol and (@i := @i + 1) < 100
where t1.col != 'someval'
set t1.col = 'someval';

Here I'm updating the first/next 100 rows that match the join criteria - you can set it to whatever number you like.

Answer 2

See here for low priority updates:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_low_priority_updates

Answer 3

If possible you could first select the ID's of the rows that you will be updating, then run the update query on this subset of ID's

Update tbl set MyKey = 'Updated' where ID between 1000 AND 2000