SEO

I've got to update a table with data from other table.
I calculated the cost of all our products and services (over 500,000.-
rows) and I need to apply the cost to each and every transaction (7.5
million rows) for the year 2006.

One table is the transaction, which is partitioned using year as the
range and is indexed in on both where conditions.
The second table is costs, and is indexed as well.

The update is taking forever... it's been running for 30 hours and I'm
starting to think it's stuck or something else. Even though the
process list says "sending data"

The EXPLAIN and EXPLAIN PARTITIONS of the equivalent select seems ok,
there is enough disk space... enough temp space... don't know what's
happening.

I'm using this statement:
/*------------------------------------------*/
UPDATE `ctacte_f_costo`, `costoporcodigo`
SET
`ctacte_f_costo`.`costo` =
(`costoporcodigo`.`costo`*`ctacte_f_costo`.`CANT`) ,
`ctacte_f_costo`.`procesado`= 1
WHERE
`costoporcodigo`.`tipo` = `ctacte_f_costo`.`TIPOPR` and
`costoporcodigo`.`codval` = `ctacte_f_costo`.`CODVAL` and
`ctacte_f_costo`.f_movfis between '2006-01-01' and '2006-12-31';
/*------------------------------------------*/

I'm considering if this other sintax might be faster

UPDATE `ctacte_f_costo`
SET
`ctacte_f_costo`.`procesado`= 1,
`ctacte_f_costo`.`costo` = (
SELECT
t1.`costo`*`ctacte_f_costo`.`CANT` as costo
FROM
`costoporcodigo` t1
WHERE
t1.`tipo` = `ctacte_f_costo`.`TIPOPR` and
t1.`codval` = `ctacte_f_costo`.`CODVAL`)
WHERE
`ctacte_f_costo`.f_movfis between '2006-01-01' and '2006-12-31';


Any advice is welcome... (please, the idea is ADVICE... please no "why
do you want to do that" or other replies that don't answer.
Please remember that this question might be asked by other ppl and the
idea is finding answers.. not more questions.
THX