sql - 甲骨文合并

Question

我正在尝试通过以下代码执行更新:

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     

我收到以下错误:

"EMAIL"."CF": invalid identifier

我尝试运行 WITH 语句的唯一查询，它运行正确。

我多次查看合并语法，但我无法找到我的错误。

请问有人能帮帮我吗？

Answer 1

您的 email 仅在 using block 中使用。从外部声明中删除它:

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