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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…