Re: Lock timeout
Hi Jay,
This is a table where the supplier of the data cannot guarantee a unique key in the data. So I added a autoincrement integer PK. They supply the data as a full replacement set grouped on asset_id. Realise that all these queries are auto generated from metadata about the record structures coming in, thus the useless query without my scrutiny. Clearly need to add the target tables uses a auto increment PK to the metadata ;)
Now that I look at this query I realise it is a waste of time. The on dup update is only useful when there is a naturally occuring PK. So the following I constructed on a query for a slightly shorter table with the same characteristics does not even parse, gives me a Error code 1064.
INSERT INTO CONVERTIBLE_HURDLE_INFO (HRDL_REDEM_CD, HRDL_END_DT, HRDL_DAYS_VAL, HRDL_VAL, HRDL_DURATION_VAL, HRDL_ALLOC_CD, HRDL_REDEM_PX, ASSET_ID, HRDL_PRICE_CD,
HRDL_VAL_CD, HRDL_CURR_CD, HRDL_EFF_DT ) VALUES ( 'C', '2006-06-15', 30, 150, 20, 'N', 100, 991765495477529, 'C', 'C', 'USD', '2006-06-15')
ON DUPLICATE KEY UPDATE HRDL_REDEM_CD='C', HRDL_END_DT='2006-06-15', HRDL_DAYS_VAL=30, HRDL_VAL=150, HRDL_DURATION_VAL=20, HRDL_ALLOC_CD='N',
HRDL_REDEM_PX=100, ASSET_ID=991765495477529, HRDL_PRICE_CD='C', HRDL_VAL_CD='C', HRDL_CURR_CD='UDSD', HRDL_EFF_DT='2006-06-15'
So I will change approach for these tables where they do not guarantee a naturally occuring PK. For each asset_id group delete all existing with that asset_id then insert all the new ones using batch insert.
Thx.
David