CODEWITHSUNDEEP
oracle-databasemergerow-number
Salvador
Salvador

Reputation: 55

MERGE WITH ROW_NUMBER

I am trying to perform a merge to insert and update as the case may be in a table. However in table 1, I only want it to insert the unique values ​​of the tbl2.rfc field regardless if my other fields change, I only want to show the unique values ​​of said field. I am occupying the ROW_NUMBER function to bring only unique values, but I have not been able to add this function to my merge.

MERGE INTO   B69_TBL1 tbl1
    USING  (SELECT T1.*, ROW_NUMBER() OVER (PARTITION BY T1.rfc ORDER BY T1.rfc  DESC) ENUMERADO                 
    FROM B69_TBL2 tbl2 
    
WHEN MATCHED THEN
UPDATE SET
tbl1.id_tbl1 = tbl2.id_con,
tbl1.rfc = tbl2.rfc,
tbl1.rfc = tbl2.name_cont

WHEN NOT MATCHED THEN
INSERT (tbl1.id_tbl1,tbl1.tbl1tipo,tbl1.id_concentrado,tbl1.rfc,
        tbl1.name_cont,tbl1.baja_logica,tbl1.last_update)
VALUES (id_tbl1autt.nextval,'1','1',tbl2.id_concentrado,tbl2.rfc,
        tbl2.name_cont,'0', '11/05/2021')
        ) T1
WHERE ENUMERADO=1 
AND RFC IS NOT NULL

The error it marks is 00907. 00000 - "missing right parenthesis"

because I had it like this and it fails precisely in the on.

MERGE INTO   B69_TBL1 tbl1
    USING  (SELECT T1.*, ROW_NUMBER() OVER (PARTITION BY T1.rfc ORDER BY T1.rfc  DESC) ENUMERADO    
    ON   tbl1.rfc = tbl2.rfc
    FROM B69_TBL2 tbl2 )
    
WHEN MATCHED THEN
UPDATE SET
tbl1.id_alert = tbl2.id_con,
tbl1.rfc = tbl2.rfc,
tbl1.rfc = tbl2.name_cont

WHEN NOT MATCHED THEN
INSERT (tbl1.id_alert,tbl1.alertype,tbl1.id_con,tbl1.rfc,
        tbl1.name_cont,tbl1.baja_logica,tbl1.last_update)
VALUES (id_tbl1autt.nextval,'1','1',tbl2.id_con,tbl2.rfc,
        tbl2.name_cont,'0', '11/05/2021')
        ) T1
WHERE ENUMERADO=1 
AND RFC IS NOT NULL

Upvotes: 0

Views: 1582

Answers (1)

MT0
MT0

Reputation: 168623

  • Your ON clause is in the wrong place;
  • You had the tbl2 alias inside the USING query instead of following it;
  • You do not need the tbl1 aliases before the destination column names in the UPDATE or INSERT;
  • You are updating the rfc column twice (I removed the second instance);
  • There was a strange T1 alias after the insert that should be inside the USING query; and
  • You can use a DATE literal rather than a string.

There may be other errors, as I do not have your tables to test the query.

MERGE INTO   B69_TBL1 tbl1
USING  (
  SELECT T1.*,
         ROW_NUMBER() OVER (PARTITION BY T1.rfc ORDER BY T1.rfc  DESC) AS ENUMERADO
  FROM   B69_TBL2 t1
)  tbl2
ON tbl1.rfc = tbl2.rfc
WHEN MATCHED THEN
  UPDATE SET
    id_alert = tbl2.id_con,
    rfc = tbl2.rfc
WHEN NOT MATCHED THEN
  INSERT (
    id_alert,
    alertype,
    id_con,
    rfc,
    name_cont,
    baja_logica,
    last_update
  ) VALUES (
    id_tbl1autt.nextval,
    '1',
    '1',
    tbl2.id_con,
    tbl2.rfc,
    tbl2.name_cont,
    '0',
    DATE '2021-05-11'
  )
  WHERE ENUMERADO=1 
  AND   RFC IS NOT NULL

Upvotes: 2

Related Questions