CREATE TABLE TEMP
(id MEDIUMINT NOT NULL AUTO_INCREMENT,
F1 smallint,
F2 timestamp,
F3 char,
F4 varchar(200),
F5 int,
F6 int,
F7 int,
F8 varchar(200),
PRIMARY KEY (id)
)
CREATE UNIQUE INDEX in_temp
ON TEMP (F1, F4, F5, F6, F7, F8);
insert TEMP (F1, F2, F3, F4, F5, F6, F7, F8)
select F1, F2, F3, F4, F5, F6, F7, F8
from MYTAB1 where F2 between '2011-04-15 06:06:20' and '2011-04-15 23:59:59'
(Source table MYTAB1 with BAD values of F2 . MYTAB1 has 900K records)
(I create a subset of the HUGE table MYTAB1 with the problem fields only. TEMP has 500K records)
(MYTAB2 has GOOD F2 field values)
update TEMP b
set b.F2 = (select a.F2
from MYTAB2 a where
a.F1 = b.F1
and a.F2 = b.F2
and a.F4 = b.F4
and a.F5 = b.F5
and a.F6 = b.F6
and a.F7 = b.F7
and a.F8 = b.F8)
(Updating 500K records)
DELETE from MYTAB1 where F2 between '2011-04-15 06:06:20' and '2011-04-15 23:59:59';
INSERT INTO MYTAB1 SELECT * FROM TEMP;