drop table test;

create table test(

	pk integer primary key, 

    val varchar(20) not null,

    lt_timestamp timestamp not null default current_timestamp() on update current_timestamp());

insert into test(pk, val) values(1, 'val1');

select * from test;

+----+------+---------------------+

| pk | val  | lt_timestamp        |

+----+------+---------------------+

|  1 | val1 | 2023-05-02 07:47:19 |

+----+------+---------------------+

-- when both are to insert works correctly

insert into test(pk, val) 

	select 3, 'val3' from dual

	union

	select 4, 'val4' from dual

    on duplicate key update lt_timestamp=now();

+----+------+---------------------+

| pk | val  | lt_timestamp        |

+----+------+---------------------+

|  1 | val1 | 2023-05-02 07:47:19 |

|  3 | val3 | 2023-05-02 07:48:02 |

|  4 | val4 | 2023-05-02 07:48:02 |

+----+------+---------------------+

-- but when one is updated and one is inserted, the inserted ne is initialized to '0000-00-00 00:00:00'

insert into test(pk, val) 

	select 1, 'val1' from dual

	union

	select 2, 'val2' from dual

    on duplicate key update lt_timestamp=now();

+----+------+---------------------+

| pk | val  | lt_timestamp        |

+----+------+---------------------+

|  1 | val1 | 2023-05-02 07:51:56 |

|  2 | val2 | 0000-00-00 00:00:00 |

|  3 | val3 | 2023-05-02 07:51:18 |

|  4 | val4 | 2023-05-02 07:51:18 |

+----+------+---------------------+

-- see inserted record 2 with wrongly initialized timestamp

-- when change order and insert second as first datetime is initialized correctly