1

I want to insert a row in a table if the keys are not in the table. If the keys are already in the table, I want to update two timestamps on the row as shown below:

Inputs to this routine include
lEarliest = TIMESTAMP
lLatest = TIMESTAMP

BEGIN
MERGE INTO mytable
USING dual ON (id1 = ? AND ...)
WHEN NOT MATCHED THEN
INSERT (...) VALUES (...)
WHEN MATCHED THEN
UPDATE SET earliest_timestamp = lEarliest where earliest_timestamp > lEarliest;
<--------- How can I add this second if statement to the Oracle MERGE clause? --------->
UPDATE SET latest_timestamp = lLatest where latest_timestamp < lLatest;
END;
/
GMB
  • 216,147
  • 25
  • 84
  • 135
Jared
  • 73
  • 6

1 Answers1

2
...
when matched then update
set   earliest_timestamp = least   (earliest_timestamp, lEarliest),
      latest_timestamp   = greatest(latest_timestamp  , lLatest)
where earliest_timestamp > lEarliest or latest_timestamp < lLatest
...

This way if, for example, earliest_timestamp > lEarliest but latest_timestamp >= lLatest, earilest_timestamp will be updated as desired while latest_timestamp will be updated to itself.

This is not even "extra work" since "extra work" is caused by entire rows that must be updated (so it's not a good idea to "update" a row where no value has changed); in this case, the entire row is updated, since at least one of the values changes.

  • Thank you @mathguy. It is working now. Really good and elegant solution – Jared Aug 24 '20 at 20:00
  • Hi GMB, I realized that I have to update one more variable when executing the above SQL. It is my bad that I didn't post that in the first instance. Is it possible to solve the problem mentioned below? ``` BEGIN MERGE INTO mytable USING dual ON (id1 = ? AND ...) WHEN NOT MATCHED THEN INSERT (...) VALUES (...) WHEN MATCHED THEN UPDATE SET earliest_timestamp = lEarliest, earliest_id = lEarliestId where earliest_timestamp > lEarliest; UPDATE SET latest_timestamp = lLatest, latest_id = lLatestId where latest_timestamp < lLatest; END; / ``` – Jared Aug 25 '20 at 16:12