I've been experimenting with the MERGE INTO statement using comparison operators that could potentially result in a non-equijoin set of rows.
Oracle's MERGE INTO statement allows the use of comparison operators that would produce a non-equijoin result set. The end result of this would be (if not for the ORA-30926 error):allowing the same row in the target table to be updated multiple times over and over again.
While Oracle's SQL parser engine doesn't complain about the syntax it will potentially complain when the MERGE INTO statement is executed if multiple rows are returned for a single-row in the outer table.
You will get this error: ORA-30926: unable to get a stable set of rows in the source tables.
I value the decision by Oracle (or any software developer or organization for that matter) to allow the end-user to hang themselves by executing dangerous statements that depend on the user knowing what they are doing. But allowing the MERGE INTO statement to join tables on a not equal join condition is really very surprising to me. The performance hit could be disastrous in the real word with a large data set. It doesn't seem to make very good sense from a purely logical standpoint.
The only scenario that I could think of off the top of my head that would barely make any logical sense to use a non-equijoin condition to join 2 tables would be where you needed to make sure you had the latest piece of data among all potential matching rows.
But this certainly seems like the sledgehammer approach. Should it just be restricted all together?
At the end of the day, the MERGE INTO statement fails if multiple-rows are returned so Oracle makes a good decision there. I would vote for throwing a syntax error before it even executes, or even showing a warning before execution in future versions if this is attempted. But I guess warnings are not a thing when executing SQL queries. At least not to my knowledge.