Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
329 views
in Technique[技术] by (71.8m points)

Oracle Sql Statement for unique timestamp for each row

We have following table on an Oracle DBMS (used by a legacy application), where a timestamp is part of the key (I know there would be better ways...)

TABLE ITEM_HISTORY (
 ITEM_ID number,
 MY_TIMESTAMP TIMESTAMP(7),
 ... 
 PRIMARY KEY (ITEM_ID, MY_TIMESTAMP));

Only the combination of ITEM_ID and MY_TIMESTAMP is unique. On a running system we need to guarantee now, that every timestamp is unique, because we have to make some corrections of corrupt data and with given data we violate the unique key constraint. With a simple MY_TIMESTAMP = SYSTEMTIMESTAMP update, all rows will have the same timestamp.

How can I update my Table with SQL, so that every row has an unique timestamp?

Update / Explanation:

Because data got corrupted at our client, I only can try to correct the items as good as possible. In some cases, items (with different ITEM_IDs) in the corrupted table will be corrected to the same item with same ITEM_ID. To make this update, I must guarantee before that the timestamps are different.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

The following UPDATE statement will guarantee that each row has a unique MY_TIMESTAMP value, by increasing the milliseconds by the rownum value.

EDIT: After Alessandro Rossi pointed out that there could be duplicate values, the following query has been modified to use SYSTIMESTAMP for the update.

  UPDATE ITEM_HISTORY 
  SET my_timestamp = SYSTIMESTAMP + NUMTODSINTERVAL(rownum/1000, 'SECOND');

However, it is recommended that you use an alternative strategy, such as adding another column to store the key value.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...