Geek Answers Handbook

How to update only a few lines at a time? (Syntax with multiple tables)

Is there a way without using a script to run UPDATE that will not slow down the database, only by doing a certain number of rows at a time?

I am doing

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

and he is going to update 3,171 rows in a very large table.

I do not want to block the database or slow it down. Any ideas?

Note:

According to MySQL docs for UPDATE: for syntax with multiple tables, UPDATE updates the rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.

I did an INNER JOIN and LIMIT was not allowed to be used using LIMIT, since the solution does not work in this case.

+5
source share
3 answers

, LIMIT!

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

, , , !

. !

. , :

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';

/ 100 , - , .

+5

IF possible, you could first select the identifier of the rows that you will update, and then run a request to update a subset of identifiers

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

More articles:


All Articles