Oracle SQL - Update procedure only non null values

Question

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

Do you want to avoid doing an update on the field? Or do you want to avoid changing the value? So if CUSTOMER_BIRTHDAY in the table is NULL and a CustomerBirthday of January 12, 1986 is passed in, you don't want to update the table? That seems odd. Perhaps you mean that you only want to change the data if the parameter values passed in to your procedure call are non-NULL? I'm also confused as to why your CustomerBirthday is a TIMESTAMP rather than a DATE-- it seems unlikely that you're really asking a customer to provide their birth date to the millisecond. — Justin Cave
– Justin Cave, Commented Feb 26, 2015 at 19:58
Hello, This SP is a simple example and yes, the timestamp is wrong :) If the variables CustomerGender, CustomerBirthday or any one of them comes to null, I do not want to update, do not want these values appear to null. Do you understand? — user2852514
– user2852514, Commented Feb 26, 2015 at 20:03
Do you want to avoid doing the update? Or do you want to avoid changing the value in the column? My guess is that you merely want to avoid changing the value in the column. But you may be trying to avoid updating the column (even to the same value) to avoid the cost of writing the redo or to avoid making changes that would need to get replicated somewhere. — Justin Cave
– Justin Cave, Commented Feb 26, 2015 at 20:08
I want to avoid changing the value in the column. But do not know how to do. — user2852514
– user2852514, Commented Feb 26, 2015 at 20:29

Justin Cave · Accepted Answer · 2015-02-26 20:33:21Z

5

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.

answered Feb 26, 2015 at 20:33

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL - Update procedure only non null values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related