Dynamic Update Query in Oracle
oracle, plsql, sql, sql-update
Solution
You could write a single `UPDATE`, and use `DECODE` (or `CASE`) to only update the dates when `in_project_status` matches:
...
, submitted_date = DECODE( in_project_status, 'SUB', SYSDATE, submitted_date )
, approved_date = DECODE( in_project_status, 'APP', SYSDATE, approved_date )
...
This would avoid the duplicate `UPDATE`.
Problem
I'm 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 example: ``` 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 submitted by and approved by. So if the status is set to submitted or approved, I want those 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 the REVISION_COUNT and number of rows affected (rowcount) to check if the update was successful or not. What is the best way to write this query? I'm assuming a Dynamic query is better than having an if-elsif-else statement with the whole query nearly duplicated in each block.