Delete data sequentially from a large mysql table

Shinigaloo — Mon, 11 Nov 2019 16:19:01 GMT

Hi,
I would like to make sequential deletions of a mysql table for example: delete 10000 lines by 10000 lines for a given condition (like : where operator_id=39),
using either a loop or a component that makes deletions by data block.

like ( while : nbrows >0 ---> delete from table xxx where operator_id=39 limit 10000) or something like that.

Any Help please ?

thx you.

Re: Delete data sequentially from a large mysql table

Anonymous — Tue, 19 Nov 2019 04:44:43 GMT

Hi
Yes, you need to use a tLoop to loop the execution until the nbrows=0, eg:
tLoop-iterate--tMysqlRow--oncomponent--tMysqlInput--main--tjavaRow

Define a context variable with boolean type, let's call it 'condition', set the default value as true;
On tLoop, select 'while' type and set the condition with context variable:context.condition
on tMysqlRow, execute the deletion statement.
"delete from tableName where operator_id=39 limit 10000"
on tMysqlInput, define one column with int type, let's call it nb_rows, query the number of rows,
"select count(*) from tableName where operator_id=39"

on tJavaRow:
if(input_row.nb_rows>0){
context.condition=true;
}else{
context.condition=false;
}

Hope it helps you!

Regards
Shong

topic Re: Delete data sequentially from a large mysql table in Talend Studio

Delete data sequentially from a large mysql table

Re: Delete data sequentially from a large mysql table