MySQL Forums
Forum List  »  InnoDB

Re: Lock timeout
Posted by: David Wynter
Date: July 07, 2005 01:34AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4252
June 30, 2005 02:43PM
2242
July 06, 2005 03:56PM
2577
July 06, 2005 04:54PM
Re: Lock timeout
2537
July 07, 2005 01:34AM
2278
July 07, 2005 06:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.