Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple SQL update
Randy Harris wrote:
> I'm trying to do an update in Oracle 9i that seems like it should be
> very simple but I can't figure out the syntax. The update needs to use
> a different formula Dependant on the current value of the attribute.
>
> Simplified:
>
> UPDATE MyTable Set F1 = (F1 > 1) * 2 + 1 ;
>
> This returns a missing right parenthesis error.
>
> As does
>
> Select (2>1) FROM dual ;
>
> How do I put the comparison into the calculation?
>
> Randy Harris
> tech at promail dot com
Try an experiment using SIGN - the following determines if 2 (or a
column name if you replace 2 with the column name) is greater than 1
SELECT
DECODE(SIGN(2-1), 1, 'GREATER THAN 1', 0, 'EQUAL TO 1', -1, 'LESS
THAN 1')
FROM
DUAL;
If that gives you what you need, try something like this:
SELECT
DECODE(SIGN(F1 - 1), 1, F1 * 2 + 1, 0, 1, -1, 1)
FROM
MYTABLE;
If that give you what you need, you can further shorten the syntax:
SELECT
DECODE(SIGN(F1 - 1), 1, F1 * 2 + 1, 1)
FROM
MYTABLE;
The above can be thought of as follows:
If (F1 - 1) is greater than 0 (logically equivalent to F1 is greater
than 1), then return F1 * 2 + 1
else return 1
SIGN returns 1 if the value being evaluated is greater than 0, 0 if the value being evaluated is 0, and -1 if the value being evaluated is less than 0.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Sun Aug 13 2006 - 08:32:16 CDT