Oracle sql - join multiple tables in merge query

Question

I need to get transaction_dates in transactions table to write on a column in a table named payment_plan. But i need to do this operation just for polices that has payment_method as directdebit and cash . So i built follwing query;

MERGE INTO PAYMENT_PLAN pp
USING
(
SELECT pt.transaction_date
    FROM payment_plan pp
    JOIN transactions pt ON (pp.transaction_id = pt.transaction_id)
    JOIN policy pol ON (PP.POLICY_ID = POL.POLICY_ID)
    WHERE pp.payment_plan_status = 'CLOSED'
    AND POL.PAYMENT_METHOD IN ('Cash','DirectDebit')
    AND pp.bank_clearance_date IS NULL
    AND pp.transaction_id IS NOT NULL
    AND pt.transaction_date IS NOT NULL 
) ta ON (ta.transaction_id = pp.transaction_id)
WHEN MATCHED THEN UPDATE 
SET pp.bank_clearance_date = ta.transaction_date,
pp.effective_date = ta.transaction_date,
ta.receipt_date = ta.transaction_date;

It gets an " ORA-00904: "TA"."TRANSACTION_ID": Invalid Identifier" error.

So, how can i get transaction_date columns of cash and direct_debit policies?

Answer 1

In the query in the USING clause you only SELECT pt.transaction_date and do not include transaction_id so the identifier is unknown. If you want to use that column then you have to include it in the SELECT clause.

MERGE INTO PAYMENT_PLAN pp
USING (
  SELECT pt.transaction_date,
         pp.transaction_id
  FROM   payment_plan pp
         JOIN transactions pt
         ON (pp.transaction_id = pt.transaction_id)
         JOIN policy pol ON (PP.POLICY_ID = POL.POLICY_ID)
  WHERE  pp.payment_plan_status = 'CLOSED'
  AND    POL.PAYMENT_METHOD IN ('Cash','DirectDebit')
  AND    pp.bank_clearance_date IS NULL
  AND    pp.transaction_id      IS NOT NULL
  AND    pt.transaction_date    IS NOT NULL 
) ta ON (ta.transaction_id = pp.transaction_id)
WHEN MATCHED THEN
  UPDATE 
  SET pp.bank_clearance_date = ta.transaction_date,
      pp.effective_date      = ta.transaction_date,
      ta.receipt_date        = ta.transaction_date;

However, if you want to update the same row then you can use the ROWID pseudo-column (which is effectively a pointer to the row):

MERGE INTO PAYMENT_PLAN pp
USING (
  SELECT pt.transaction_date,
         pp.ROWID AS rid
  FROM   payment_plan pp
         JOIN transactions pt
         ON (pp.transaction_id = pt.transaction_id)
         JOIN policy pol ON (PP.POLICY_ID = POL.POLICY_ID)
  WHERE  pp.payment_plan_status = 'CLOSED'
  AND    POL.PAYMENT_METHOD IN ('Cash','DirectDebit')
  AND    pp.bank_clearance_date IS NULL
  AND    pp.transaction_id      IS NOT NULL
  AND    pt.transaction_date    IS NOT NULL 
) ta ON (ta.rid = pp.ROWID)
WHEN MATCHED THEN
  UPDATE 
  SET pp.bank_clearance_date = ta.transaction_date,
      pp.effective_date      = ta.transaction_date,
      pp.receipt_date        = ta.transaction_date;

or you could simplify the query to:

MERGE INTO PAYMENT_PLAN pp
USING (
  SELECT pt.transaction_date,
         pt.transaction_id
  FROM   transactions pt
         ON (pp.transaction_id = pt.transaction_id)
         JOIN policy pol ON (PP.POLICY_ID = POL.POLICY_ID)
  WHERE  POL.PAYMENT_METHOD IN ('Cash','DirectDebit')
  AND    pt.transaction_date    IS NOT NULL
) ta ON (
    pp.payment_plan_status = 'CLOSED'
AND pp.bank_clearance_date IS NULL
AND pp.transaction_id      = ta.transaction_id
)
WHEN MATCHED THEN
  UPDATE 
  SET bank_clearance_date = ta.transaction_date,
      effective_date      = ta.transaction_date,
      receipt_date        = ta.transaction_date;