CODEWITHSUNDEEP
sqloracle-databasemergecommon-table-expression
Luca Leoni
Luca Leoni

Reputation: 33

oracle merge with

I'm trying to perform an update through the following code:

MERGE
INTO    target trg
USING   (
    WITH email AS (
      SELECT trim(t2.crn_0) as cf,
             coalesce(trim(t1.WEB_1),trim(t1.WEB_0)) as im
      FROM   tab1 t1 
      INNER JOIN tab2 t2 on t2.bprnum_0=t1.bpanum_0
      INNER JOIN target t3 on t3.ycodfis_0=t2.crn_0
      WHERE t1.bpaadd_0='000' 
      AND regexp_like(t3.ycodfis_0,'[^A-Za-z]') 
      AND t3.ycodfis_0 != ' '
      AND coalesce(trim(t1.WEB_1),trim(t1.WEB_0)) != ' '
      )
    SELECT email.cf, email.im FROM email
    ) ON (email.cf = trg.ycodfis_0)
WHEN MATCHED THEN UPDATE SET trg.email_0 = email.im

I am getting the following error:

"EMAIL"."CF": invalid identifier

I tryed to run the only query of the WITH statement, it runs correctly.

I looked at the merge syntax several times, thugh I am not able to find what is my mistake.

Does anybody can help me, please?

Upvotes: 3

Views: 922

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60312

Always use aliases - for example, src:

MERGE
INTO    target trg
USING   (
             ... subquery omitted for clarity ...
        ) src
ON      ( src.cf = trg.ycodfis_0 )
WHEN MATCHED THEN UPDATE SET trg.email_0 = src.im

Upvotes: 1

Ori Marko
Ori Marko

Reputation: 58892

Your email is used only inside using block. Remove it from outside statements:

ON (cf = trg.ycodfis_0)
WHEN MATCHED THEN UPDATE SET trg.email_0 = im

Upvotes: 2

Related Questions