CODEWITHSUNDEEP
sqloracle-sqldeveloper
user2852514
user2852514

Reputation: 59

Oracle SQL - Update procedure only non null values

I have a simple update procedure. So, i wish to update only fields they are not null values. How can i do it?

  PROCEDURE UpdateCustomerInfo(
      CustomerID                 IN NUMBER,
      CustomerType                 IN VARCHAR2,
      CustomerName        IN VARCHAR2,
      CustomerGender          IN VARCHAR2,
      CustomerBirthday        IN DATE)
  AS
  BEGIN
    UPDATE CUSTOMER_INFO
    SET CUSTOMER_TYPE =CustomerType,
     CUSTOMER_NAME =CustomerName,
     CUSTOMER_GENDER =CustomerGender,
     CUSTOMER_BIRTHDAY =CustomerBirthday
    WHERE CUSTOMER_ID = CustomerID;
    COMMIT;

  END CUSTOMER_INFO

Anyone can help me?

Thanks

Upvotes: 1

Views: 2059

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

UPDATE CUSTOMER_INFO
   SET CUSTOMER_TYPE     = NVL(CustomerType, CUSTOMER_TYPE),
       CUSTOMER_NAME     = NVL(CustomerName, CUSTOMER_NAME),
       CUSTOMER_GENDER   = NVL(CustomerGender, CUSTOMER_GENDER),
       CUSTOMER_BIRTHDAY = NVL(CustomerBirthday, CUSTOMER_BIRTHDAY)
 WHERE CUSTOMER_ID = CustomerID;

should do it. The NVL function will return the second parameter if the first parameter is NULL. So if the CustomerType parameter is NULL, the CUSTOMER_TYPE column will be updated to have the value that it already has. You're still doing the update (generating redo for example) but you're not changing the data.

Upvotes: 5

Related Questions