CODEWITHSUNDEEP
sqloracle-databaseinputparameters
htrufan
htrufan

Reputation: 261

Oracle update field A or field B depending on input parameter

I would like to be able to choose which field in my table is updated by my stored procedure based on an input parameter's value. If the input field is equal to 'draft' it should update id_draft, otherwise it should update id. Is this possible?

This is one of my failed attempts that may help illustrate what I want to do:

CREATE OR REPLACE PROCEDURE sp_test (input_field VARCHAR DEFAULT NULL)
 IS
   BEGIN
      UPDATE TABLE
      SET
        CASE
            WHEN input_field = 'draft' THEN id_draft
            ELSE id
   END = 'x'

Upvotes: 2

Views: 1928

Answers (1)

MatBailie
MatBailie

Reputation: 86765

You can't use CASE statement to alter the query structure itself. It only returns data, it's a function.

What you have (CASE WHEN input_field = 'draft' then id_draft ELSE id END) returns the value in either the id field or the id_draft field. It's like doing this...

UPDATE
  yourTable
SET
  CASE WHEN 'abc' = 'draft' THEN 123 ELSE 789 END = 'x'

Instead, you need to put the CASE statement on the right hand side...

UPDATE
  yourTable
SET
  id       = CASE WHEN input_field = 'draft' THEN id  ELSE 'x'      END,
  id_draft = CASE WHEN input_field = 'draft' THEN 'x' ELSE id_draft END

But, actually, you may just be better off doing this...

IF (input_field = 'draft')
  UPDATE yourTable SET id_draft = 'x'
ELSE
  UPDATE yourTable SET id = 'x'

EDIT:*

To use a value from another table instead of just 'x', you can use something like this...

UPDATE
  yourTable
SET
  id       = CASE WHEN input_field = 'draft' THEN yourTable.id  ELSE otherTable.x       END,
  id_draft = CASE WHEN input_field = 'draft' THEN otherTable.x  ELSE yourTable.id_draft END
FROM
  otherTable
WHERE
  otherTable.a=? AND otherTable.b=?

Upvotes: 2

Related Questions