Dynamic update request in Oracle

Question

I am trying to create a standard UPDATE query for a table. However, if certain criteria are met, some columns should be included / excluded from the UPDATE statement.

For instance:

 UPDATE TBL_PROJECT SET
     REVISION_COUNT = V_REVISION_COUNT
    ,PRIMARY_BRANCH = IN_PRIMARY_BRANCH
    ,PROJECT_STATUS = IN_PROJECT_STATUS
    ...
  WHERE PROJECT_ID = IO_PROJECT_ID
  AND   REVISION_COUNT = IO_REVISION_COUNT
  RETURNING REVISION_COUNT INTO IO_REVISION_COUNT';

However, the table has two columns for presentation and approval. Therefore, if the status is set for submission or approval, I want these columns to be updated. eg.

IF IN_PROJECT_STATUS = 'SUB'
  UPDATE TBL_PROJECT SET
    SUBMITTED_DATE = SYSDATE
ELSIF IN_PROJECT_STATUS = 'APP'
  UPDATE TBL_PROJECT SET
    APPROVED_DATE = SYSDATE
END;

I also need to return REVISION_COUNT and the number of rows affected (rowcount) to check if the update was successful or not.

What is the best way to write this query? I assume that a dynamic query is better than with an if-elsif-else statement, with the entire query being almost duplicated in each block.

Answer 1

UPDATE DECODE ( CASE) , in_project_status :

...
, submitted_date = DECODE( in_project_status, 'SUB', SYSDATE, submitted_date )
, approved_date  = DECODE( in_project_status, 'APP', SYSDATE, approved_date )
...

UPDATE.

Answer 2

:

UPDATE TBL_PROJECT 
  SET REVISION_COUNT = v_revision_count,
      PRIMARY_BRANCH = IN_PRIMARY_BRANCH,
      PROJECT_STATUS = IN_PROJECT_STATUS
      ...
      SUBMITTED_DATE = CASE WHEN IN_PROJECT_STATUS = 'APP' THEN SYSDATE ELSE SUBMITTED_DATE END,
      APPROVED_DATE = CASE WHEN IN_PROJECT_STATUS = 'SUB' THEN SYSDATE ELSE APPROVED_DATE END,
WHERE PROJECT_ID = IO_PROJECT_ID
  AND REVISION_COUNT = IO_REVISION_COUNT
RETURNING REVISION_COUNT INTO IO_REVISION_COUNT