CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
HEEN
HEEN

Reputation: 4721

Update the table if data entered for the same SAP_ID again in PL SQL

I have a SP where I have written the logic for inserting records into the table. But now if User enters the record _for same SAP_ID again_ then I want to update that record.

So how to update the record if added for same SAP_ID again ??

Below is my Stored procedure:-

    PROCEDURE INSERT_INTO_EXIST_SAPID
  (
 P_SAPID IN NVARCHAR2,
 P_VENDOR_NAME IN NVARCHAR2,
 P_ODU_MODEL IN NVARCHAR2,
 P_ANT_MODEL IN NVARCHAR2,
 P_OMT_MODEL IN NVARCHAR2,
 P_QUANTITY IN NVARCHAR2,
 P_CREATED_BY IN NVARCHAR2,
 P_CREATED_DATE IN NVARCHAR2,
 P_IS_EXISTSAP IN CHAR,
 PREQUEST_ID OUT NUMBER     
 )

 AS BEGIN

INSERT INTO UBR_STRUCTURE_DETAILS
                                  (
                                    RJ_SAPID,
                                    VENDOR_NAME,
                                    ODU_EQP_MODEL,
                                    ANT_EQP_MODEL,
                                    OMT_EQP_MODEL,
                                    MODEL_COUNT,
                                    CREATED_USER,
                                    CREATED_DATE,
                                    EXIST_SAPID
                                  )
       VALUES

                                 (
                                   P_SAPID,
                                   P_VENDOR_NAME,
                                   P_ODU_MODEL,
                                   P_ANT_MODEL,
                                   P_OMT_MODEL,
                                   P_QUANTITY,
                                   P_CREATED_BY,
                                   SYSDATE,
                                   P_IS_EXISTSAP
                                 )returning REQUEST_ID INTO PREQUEST_ID;
 END INSERT_INTO_EXIST_SAPID;

Upvotes: 0

Views: 48

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175776

You could use MERGE statement:

MERGE INTO  UBR_STRUCTURE_DETAILS trg
USING (SELECT P_SAPID,
              P_VENDOR_NAME,
              P_ODU_MODEL,
              P_ANT_MODEL,
              P_OMT_MODEL,
              P_QUANTITY,
              P_CREATED_BY,
              SYSDATE,
              P_IS_EXISTSAP
       FROM dual) src
    ON trg.<col_to_match> = src.<col_to_match>
WHEN MATCHED THEN
       UPDATE SET
          col_name = src.col_name
WHEN NOT MATCHED THEN
       INSERT (col_list)
       VALUES (...);

Upvotes: 2

Related Questions