MySQL Bulk update instructions and sample

Instead of updating multiple records via perl or php loop, it can be done totally using mysql scripts.
- create temporary table and fill it in with the data that would be used to update your target table
- update target table joined with the temporary table and set the records fields values to the values of the temp table records.

this technique was used in 4h_cron.pl and a huge gain of performance was achieved.

#Update ask_count
$dbh->do(
qq{
create temporary table tmp_questions_text(
id integer auto_increment,
question integer,
ask_count integer,
primary key (id)
)
}
);

$dbh->do(
qq{
insert into tmp_questions_text (question, ask_count)
select question, count(question) as ask_count
from questions
group by question
}
);

$ret_info->{updated_questions} = int(
$dbh->do(
qq{
update questions_text
join tmp_questions_text
on questions_text.id = tmp_questions_text.question
and questions_text.ask_count <> tmp_questions_text.ask_count
set questions_text.ask_count = tmp_questions_text.ask_count
}
)
);