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