| Examples: what works and what doesn't Imagine what would happen if we had the value 1 in the column src.id more than once, with different values for src.val. Obviously, the update would make no sense (in ANY database - that's a logical issue). Now, we know that there are no duplicates in src.id, but the Oracle engine doesn't know that - so it's complaining. Perhaps this is why so many practitioners believe Oracle "doesn't have UPDATE with joins"? What Oracle expects is that src.id should be unique, and that it, Oracle, would know that beforehand. Easily fixed! Note that the same works with composite keys (on more than one column), if the matching for the update needs to use more than one column. In practice, src.id may be PK and tgt.id may be FK pointing to this PK, but that is not relevant for updates with join; what is relevant is the unique constraint. The same result could be achieved with a MERGE statement (which deserves its own Documentation article), and I personally prefer MERGE in these cases, but the reason is not that "Oracle doesn't do updates with joins." As this example shows, Oracle does do updates with joins. |