Hello All,

I was just updating a table in oracle9.2 by hand and bumped into this.

Following seems to be the valid syntax in oracle.

Update foo set somefield=NULL where somefield >9;

Now I am not sure having something equalled with NULL is a good thig logically.
I would say

Update foo set somefield [to] NULL where somefield >9;

sounds much better. Postgresql uses =default expression which is fine.

Is Oracle behaviour correct?


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Mon, 15 Sep 2003, Shridhar Daithankar wrote:
[color=blue]
> I was just updating a table in oracle9.2 by hand and bumped into this.
>
> Following seems to be the valid syntax in oracle.
>
> Update foo set somefield=NULL where somefield >9;
>
> Now I am not sure having something equalled with NULL is a good thig logically.
> I would say
>
> Update foo set somefield [to] NULL where somefield >9;
>
> sounds much better. Postgresql uses =default expression which is fine.
>
> Is Oracle behaviour correct?[/color]

Yes (and we also allow update foo set somefield=NULL).

<set clause list> ::=
<set clause> [ { <comma> <set clause> }... ]

<set clause> ::=
<object column> <equals operator> <update source>

<update source> ::=
<value expression>
| <null specification>
| DEFAULT

<object column> ::= <column name>

and null specification is:

<null specification> ::=
NULL


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:[color=blue]
> Following seems to be the valid syntax in oracle.[/color]
[color=blue]
> Update foo set somefield=NULL where somefield >9;[/color]

AFAIK that's valid syntax in Postgres and the SQL standard, too.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Mon, 15 Sep 2003 19:34:48 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:[color=blue]
>Update foo set somefield=NULL where somefield >9;
>
>Now I am not sure having something equalled with NULL is a good thig logically.[/color]

It doesn't matter whether I agree. The standard does not. SQL92 says

<set clause> ::=
<object column> <equals operator> <update source>

<equals operator> ::= =

<update source> ::=
<value expression>
| <null specification>
| DEFAULT

<null specification> ::=
NULL

SQL99 is much more verbose and difficult to read, but it is very clear
that the assignment operator in a set clause has to be "=".

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On 15 Sep 2003 at 11:17, Tom Lane wrote:
[color=blue]
> "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:[color=green]
> > Following seems to be the valid syntax in oracle.[/color]
>[color=green]
> > Update foo set somefield=NULL where somefield >9;[/color]
>
> AFAIK that's valid syntax in Postgres and the SQL standard, too.[/color]

I got confused between equality operator and assignment operator. Equalling
NULL is wrong, assigning it is not.

I should have had more rest after my fever..:-(

Bye
Shridhar

--
Fifth Law of Procrastination: Procrastination avoids boredom; one never has the
feeling that there is nothing important to do.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

It's just an assignment statement, how ELSE would you assign a value,
even a NULL, to a field?

Manfred Koizar wrote:
[color=blue]
>On Mon, 15 Sep 2003 19:34:48 +0530, "Shridhar Daithankar"
><shridhar_daithankar@persistent.co.in> wrote:
>
>[color=green]
>>Update foo set somefield=NULL where somefield >9;
>>
>>Now I am not sure having something equalled with NULL is a good thig logically.
>>
>>[/color]
>
>It doesn't matter whether I agree. The standard does not. SQL92 says
>
> <set clause> ::=
> <object column> <equals operator> <update source>
>
> <equals operator> ::= =
>
> <update source> ::=
> <value expression>
> | <null specification>
> | DEFAULT
>
> <null specification> ::=
> NULL
>
>SQL99 is much more verbose and difficult to read, but it is very clear
>that the assignment operator in a set clause has to be "=".
>
>Servus
> Manfred
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>[/color]


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings