CODEWITHSUNDEEP
oracle-databasemergesubquery
Salvador
Salvador

Reputation: 55

WITH, MERGE SUBQUERY

I am looking to perform a merge with a subquery, all this so that a specific RFC field is unique regardless of whether the other columns have other fields and that it can update and insert as the case may be, but in the merge it marks the following error.

"missing SELECT keyword"

WITH S AS
(
SELECT
        T1.id_conce,T1.name_cont,T1.rfc
    FROM 
    (
    SELECT T1.*, ROW_NUMBER() OVER (PARTITION BY T1.rfc ORDER BY T1.rfc  DESC) ENUM 
    FROM B69_TBL_ALERT_CONCE T1
    )T1
    WHERE ENUM=1 
AND RFC IS NOT NULL
)
 MERGE  B69_TBL_ALERT alert
    USING 
    S ON alert.rfc = s.rfc AND
    WHERE ENUM=1 
AND RFC IS NOT NULL
WHEN MATCHED THEN
UPDATE SET
alert.id_conce = T1.id_conce,
alert.name_cont = T1.name_cont
WHEN NOT MATCHED THEN
INSERT (alert.id_alert,alert.id_alerttipo,alert.id_conce,alert.rfc,
        alert.name_cont,alert.baja_logica,alert.last_update)
VALUES (id_alertaaut.nextval,'1',s.id_conce,s.rfc,
        s.name_cont,'0','11/05/2021')

Upvotes: 0

Views: 73

Answers (2)

MT0
MT0

Reputation: 168371

You want:

MERGE  B69_TBL_ALERT alert
USING (
  SELECT id_conce,            -- Don't use WITH clause, put the query in the USING clause
         name_cont,
         rfc
  FROM   (
    SELECT T1.*,
           ROW_NUMBER() OVER (PARTITION BY T1.rfc ORDER BY T1.rfc  DESC) ENUM 
    FROM   B69_TBL_ALERT_CONCE T1
  )
  WHERE  ENUM=1 
  AND    RFC IS NOT NULL
) s
ON (
      alert.rfc  = s.rfc
  AND alert.ENUM = 1         -- Use the appropriate alias for the column
  AND alert.RFC IS NOT NULL  -- Use the appropriate alias for the column
)
WHEN MATCHED THEN
  UPDATE SET
    id_conce  = s.id_conce,     -- Alias should be S not T1
    name_cont = s.name_cont     -- Alias should be S not T1
WHEN NOT MATCHED THEN
  INSERT (
    id_alert,
    id_alerttipo,
    id_conce,
    rfc,
    name_cont,
    baja_logica,
    last_update
  ) VALUES (
    id_alertaaut.nextval,
    '1',
    s.id_conce,
    s.rfc,
    s.name_cont,
    '0',
    DATE '2021-05-11'           -- Use a DATE literal rather than a string
  )

Upvotes: 1

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Your subquery must be in USING() clause:

 MERGE  B69_TBL_ALERT alert
    USING 
(SELECT
        T1.id_conce,T1.name_cont,T1.rfc
    FROM 
    (
    SELECT T1.*, ROW_NUMBER() OVER (PARTITION BY T1.rfc ORDER BY T1.rfc  DESC) ENUM 
    FROM B69_TBL_ALERT_CONCE T1
    )T1
    WHERE ENUM=1 
AND RFC IS NOT NULL
)
    S ON alert.rfc = s.rfc AND
    WHERE ENUM=1 
AND RFC IS NOT NULL
WHEN MATCHED THEN
UPDATE SET
alert.id_conce = T1.id_conce,
alert.name_cont = T1.name_cont
WHEN NOT MATCHED THEN
INSERT (alert.id_alert,alert.id_alerttipo,alert.id_conce,alert.rfc,
        alert.name_cont,alert.baja_logica,alert.last_update)
VALUES (id_alertaaut.nextval,'1',s.id_conce,s.rfc,
        s.name_cont,'0','11/05/2021')

Upvotes: 1

Related Questions