Oracle SQL, merge into: add condition only where there are multiple matches

Question

My merge into cannot run because the 'on' condition does not identify a one-to-one correspondence between the tables. I would like to solve this by asking that, only on such rows where the condition fails, the value of a third column is used to decide.

merge into A using B
on (A.id = B.id and A.date between B.startdate and B.enddate)
when matched then update set 
A.foo = B.foo
-- where B.tiecondition = 1 *

* this is not good because it operates always, while I want to use the condition only when there are multiple matches for the main "merge on" condition. This happens since, for some B.id rows, subsequent [B.startdate, B.enddate] intervals actually overlap (i.e. more than one B.foo values is possible for a given A.date). In these cases, the column B.tiecondition would allow me to make a choice among the possible matches.

I suppose the 'on' clause might be modified with something like

on (
    (A.id = B.id and A.date between B.startdate and B.enddate) 
or  (A.id = B.id and (A.date between B.startdate and B.enddate) and B.tiecondition = 1)
)

but I am not sure whether I would get the correct result or if there is a more elegant way to do this.

Perhaps I could use a left join instead, and add a couple of conditions checking when there are multiple matches in the results and keeping only the rows satisfying to the condition, but this looks a bit cumbersome too.

`A.date is between B.startdate and B.enddate` is syntactically invalid. You need to remove the `is` keyword. — MT0, Sep 19 '19 at 10:39
@Giuseppe . . . If this is all you need in the `merge`, an `update` might be simpler. — Gordon Linoff, Sep 19 '19 at 10:56

score 3 · Accepted Answer · answered Sep 19 '19 at 10:35

If I've got your requirements correct then you need to COUNT how many matches are going to occur and if there is going to be a single match then accept it (regardless of the tie_condition value) otherwise pick the match where tie_condition = 1.

Oracle Setup:

CREATE TABLE A ( id, dt, foo ) AS
SELECT 1, DATE '2019-09-19', CAST( NULL AS VARCHAR2(5) ) FROM DUAL UNION ALL
SELECT 2, DATE '2019-09-19', CAST( NULL AS VARCHAR2(5) ) FROM DUAL;

CREATE TABLE B ( id, startdate, enddate, tie_condition, foo ) AS
SELECT 1, DATE '2019-09-01', DATE '2019-09-30', 1, 'A' FROM DUAL UNION ALL
SELECT 1, DATE '2019-09-10', DATE '2019-09-20', 0, 'B' FROM DUAL UNION ALL
SELECT 1, DATE '2019-09-19', DATE '2019-09-29', 0, 'C' FROM DUAL UNION ALL
SELECT 2, DATE '2019-09-18', DATE '2019-09-20', 0, 'D' FROM DUAL;

Merge:

merge into A
using ( SELECT A.ROWID As rid,
               COUNT(*) OVER ( PARTITION BY A.ROWID ) AS num_matches,
               b.tie_condition,
               b.foo
        FROM   A
               INNER JOIN B
               ON (A.id = B.id and A.dt between B.startdate and B.enddate )
) B
on (A.ROWID = B.rid AND ( B.num_matches = 1 OR B.tie_condition = 1 ) )
when matched then
  update set A.foo = B.foo

Result:

SELECT * FROM A

Outputs:

ID | DT        | FOO
-: | :-------- | :--
 1 | 19-SEP-19 | A  
 2 | 19-SEP-19 | D

db<>fiddle here

[db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=c4d88ae35740f1bdad0612a6efe2ea22) You can also use `ROW_NUMBER` (rather than `COUNT`) it makes it a lot less clear how you are performing the join (so may give issues down the line when the code is less understandable) but it won't fail if you get a situation where table `B` can have two matching rows where `tie_condition = 1` (which hopefully, you can't get to otherwise the `tie_condition` column appears meaningless). — MT0, Sep 19 '19 at 11:10

Oracle SQL, merge into: add condition only where there are multiple matches

1 Answers1