CODEWITHSUNDEEP
oracle-database
user2412576
user2412576

Reputation: 49

Insert the record in one table from another table with some criteria in oracle

I need to insert the record into one table from another table which is exact replica of other table ,condition for putting this as when a same record (for certain condition match) is coming from another table it will update the existing record and when a brand new record come it will insert the record.

I have written the query for this as below please correct me if possible.

INSERT INTO  TEMP1   (B,C,D,PROVIDER_ID,NATIONAL_PROVIDER_IDENTIFIER,TAXONIMY_CODE,F,G,H) 
SELECT  W.B,W.C,W.D,W.PROVIDER_ID,W.NATIONAL_PROVIDER_IDENTIFIER,W.TAXONIMY_CODE,W.F,W.G,W.H
FROM TEMP2 W LEFT JOIN  TEMP1 A ON( A.provider_id = W.provider_id
AND A.NATIONAL_PROVIDER_IDENTIFIER = W.NATIONAL_PROVIDER_IDENTIFIER
AND A.TAXONOMY_CODE = W.TAXONOMY_CODE)
WHERE  W.SOURCE_ID = 'COSMOS'

Upvotes: 0

Views: 49

Answers (1)

sstan
sstan

Reputation: 36483

It's still a bit unclear to me, but it does sound like you want to be using a MERGE statement.

If so, your statement would look something like this (hoping I understood your data model)

MERGE INTO TEMP1 a
USING (
  SELECT B,C,D,PROVIDER_ID,NATIONAL_PROVIDER_IDENTIFIER,TAXONIMY_CODE,F,G,H
  FROM TEMP2
  WHERE SOURCE_ID = 'COSMOS'
) w
ON (a.provider_id = w.provider_id AND 
    a.NATIONAL_PROVIDER_IDENTIFIER = w.NATIONAL_PROVIDER_IDENTIFIER AND
    a.TAXONOMY_CODE = w.TAXONOMY_CODE
)
WHEN MATCHED THEN
  UPDATE SET a.b = w.b,
             a.c = w.c,
             a.d = w.d,
             a.f = w.f,
             a.g = w.g,
             a.h = w.h
WHEN NOT MATCHED THEN
  INSERT (a.B,a.C,a.D,a.PROVIDER_ID,a.NATIONAL_PROVIDER_IDENTIFIER,a.TAXONIMY_CODE,a.F,a.G,a.H)
  VALUES (w.B,w.C,w.D,w.PROVIDER_ID,w.NATIONAL_PROVIDER_IDENTIFIER,w.TAXONIMY_CODE,w.F,w.G,w.H);

Upvotes: 0

Related Questions