Oracle SQL: How to insert row based on criteria?

Question

I’ve a table Applicant:

ID	Received
1	food
1	rent assistance
1	bus ticket
2	food
2	rent assistance
3	food
4	bus ticket
5	null

As you can see, I've an Applicant table where applicant has received varying number of benefits. But now I need to check this candidate table and ensure all applicant gets all 3 benefits: food, rent assistance, and a bus ticket. An applicant also cannot receive this benefits twice. How to achieve this in oracle sql?

Note: I've Benefits table where benefits are mapped to a unique id (food = 100, rent assistance = 101, bus ticket = 102). I've written the name instead of a number for readability.

I looked various statements like MERGE and EXISTS, but couldn't see how this'd apply to my case.

Answer 1

You can use a MERGE statement and a PARTITIONed OUTER JOIN:

MERGE INTO applicant dst
USING (
  WITH benefits (benefit) AS (
    SELECT 'food'            FROM DUAL UNION ALL
    SELECT 'rent assistance' FROM DUAL UNION ALL
    SELECT 'bus ticket'      FROM DUAL
  )
  SELECT a.ROWID AS rid,
         a.pID,
         b.benefit
  FROM   benefits b
         LEFT OUTER JOIN applicant a
         PARTITION BY (a.pID)
         ON (a.received = b.benefit)
) src
ON (src.rid = dst.ROWID)
WHEN NOT MATCHED THEN
  INSERT (pID, Received) VALUES (src.pID, src.benefit);

Which, for the sample data:

CREATE TABLE applicant (pID, Received) AS
SELECT 1, 'food' FROM DUAL UNION ALL
SELECT 1, 'rent assistance' FROM DUAL UNION ALL
SELECT 1, 'bus ticket' FROM DUAL UNION ALL
SELECT 2, 'food' FROM DUAL UNION ALL
SELECT 2, 'rent assistance' FROM DUAL UNION ALL
SELECT 3, 'food' FROM DUAL UNION ALL
SELECT 4, 'bus ticket' FROM DUAL UNION ALL
SELECT 5, null FROM DUAL;

Then, after the MERGE, the table contains:

PID	RECEIVED
1	food
1	rent assistance
1	bus ticket
2	food
2	rent assistance
3	food
4	bus ticket
5	(null)
2	bus ticket
3	bus ticket
3	rent assistance
4	food
4	rent assistance
5	bus ticket
5	food
5	rent assistance

fiddle