mysql - Update table with connected rows -

ok, have table data (myisam):

this `table`     id | field1 | field2 | field3 | field4 | field5     1  | val1   | val2   | val3   | val4   | 1     2  | val1   | val2   | val3   | val4   | 1     3  | val2   | val1   | val4   | val3   | 1     4  | val2   | val1   | val4   | val3   | 1 

i need update table (see where clause):

create temporary table if not exists `table_temp` (     select         *             `table`             `field7` null         ,         `field8` null         ,         `field9` null );  update low_priority ignore `table` `t`, `table_temp` `t2` set     `t`.`field7`=current_timestamp(),     `t`.`field9`=`t2`.`id`     `t`.`field1` = `t2`.`field2`     ,     `t`.`field2` = `t2`.`field1`     ,     `t`.`field3` = `t2`.`field4`     ,     `t`.`field4` = `t2`.`field3`     ,     `t`.`field5` = `t2`.`field5`     ,     `t`.`field7` null     ,     `t`.`field8` null     ,     `t`.`field9` null 

after query done, have this:

this `table` after query         id | field1 | field2 | field3 | field4 | field5 | field9         1  | val1   | val2   | val3   | val4   | 1      | **3**         2  | val1   | val2   | val3   | val4   | 1      |         3  | val2   | val1   | val4   | val3   | 1      | **1**         4  | val2   | val1   | val4   | val3   | 1      | 

but need (all 4 rows needs updated):

        id | field1 | field2 | field3 | field4 | field5 | field9         1  | val1   | val2   | val3   | val4   | 1      | 3         2  | val1   | val2   | val3   | val4   | 1      | **4**         3  | val2   | val1   | val4   | val3   | 1      | 1         4  | val2   | val1   | val4   | val3   | 1      | **2** 

actually, 4 rows affected, using ignore not simple keyword, field9 have unique index.

as can understand, query each time starts same row in t2 -> have ignore , really affected 2 rows instead of 4. mean, when system update 2nd row looks start , first matching where clause 3rd row -> ignore, because when system updated 1st row, first matched row id=3. need push next row.

i tried set set:

...  set     `t`.`field7` = current_timestamp(),     `t`.`field9` = `t2`.`id`,     `t2`.`field7` = current_timestamp(),     `t2`.`field9` = `t`.`id` ... 

but didn't me.

is there anyway accomplish need?

you need update twice - 1. ascending , 2. descending

create table table1     (       `id` int,        `field1` varchar(4),        `field2` varchar(4),        `field3` varchar(4),        `field4` varchar(4),        `field5` int,       `field7` int,       `field8` int,       `field9` int unique     ) ;  insert table1     (`id`, `field1`, `field2`, `field3`, `field4`, `field5`) values     (1, 'val1', 'val2', 'val3', 'val4', 1),     (2, 'val1', 'val2', 'val3', 'val4', 1),     (3, 'val2', 'val1', 'val4', 'val3', 1),     (4, 'val2', 'val1', 'val4', 'val3', 1) ;  create temporary table if not exists `table_temp` (     select         *             `table1`             `field7` null         ,         `field8` null         ,         `field9` null    order id  );  update low_priority ignore `table1` `t`, `table_temp` `t2` set     `t`.`field7`=current_timestamp(),     `t`.`field9`=`t2`.`id`     `t`.`field1` = `t2`.`field2`     ,     `t`.`field2` = `t2`.`field1`     ,     `t`.`field3` = `t2`.`field4`     ,     `t`.`field4` = `t2`.`field3`     ,     `t`.`field5` = `t2`.`field5`     ,     `t`.`field7` null     ,     `t`.`field8` null     ,     `t`.`field9` null ;  create temporary table if not exists `table_temp2` (     select         *             `table1`             `field7` null         ,         `field8` null         ,         `field9` null     order id desc );  update low_priority ignore `table1` `t`, `table_temp2` `t2` set     `t`.`field7`=current_timestamp(),     `t`.`field9`=`t2`.`id`     `t`.`field1` = `t2`.`field2`     ,     `t`.`field2` = `t2`.`field1`     ,     `t`.`field3` = `t2`.`field4`     ,     `t`.`field4` = `t2`.`field3`     ,     `t`.`field5` = `t2`.`field5`     ,     `t`.`field7` null     ,     `t`.`field8` null     ,     `t`.`field9` null ;  select * table1 ;