Results 1 to 4 of 4

Thread: If in Update

  1. #1
    Join Date
    Mar 2004
    Posts
    50

    Arrow Unanswered: If in Update

    Hi
    How can I achieve the following :

    UPDATE abc
    IF (abc.ab) <>0 then
    SET abc.xy = (1/(abc.uv+1))
    else
    SET abc.xy = (1/(abc.uv+2))
    WHERE abc.fg = 10;

    Thanks

    Ishan.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: If in Update

    UPDATE abc
    SET xy = CASE WHEN ab <> 0 THEN (1/(abc.uv+1)) ELSE (1/(abc.uv+2)) END
    WHERE abc.fg = 10;

  3. #3
    Join Date
    Mar 2004
    Posts
    50
    Andrew,
    This thing seems to be working fine in SQL but not in PL/SQL.
    ORACLE 8.0

    If it is not supported by PL/SQL in 8.0, then what is the alternative to it.

    Thanks
    Ishan

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can only get it to work in 8.0 PL/SQL by either putting the CASE expression into a stored view definition, or by using dynamic SQL.

    The alternative is to use DECODE:

    UPDATE abc
    SET xy = DECODE( ab, 0, (1/(abc.uv+2)), (1/(abc.uv+1)))
    WHERE abc.fg = 10;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •