sql - Mysql on duplicate key update with type cast

Question

I am trying to insert/update details using the single query Issue occurs when type cast the string to date if the date string is invalid

Query which i tried is

INSERT INTO migration_contact (migration_id,migrationprocess_id,crlx_contact_id,dob)  
 SELECT migration_id,migrationprocess_id,DSN AS crlx_contact_id,
        IF(`BIR` = '', null,STR_TO_DATE(`BIR` ,'%Y%m%d')) AS  dob
 FROM migration_customer
 WHERE migrationprocess_id = 158 AND
       migration_id = 95
 ON DUPLICATE KEY UPDATE crlx_contact_id = VALUES(crlx_contact_id),
                         migration_id = VALUES(migration_id),
                         migrationprocess_id = VALUES(migrationprocess_id),
                         dob = VALUES(dob)

Error

SQLSTATE[HY000]: General error: 1411 Incorrect datetime value: '99011414' for function str_to_date

If i run the select query separately i can't able to find the issue ..it is working but using ''ON DUPLICATE KEY UPDATE'' it become issue

Answer 1

等待大神答复