Updates with NULL

On Mon, 15 Sep 2003, Shridhar Daithankar wrote:

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?

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

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:

Following seems to be the valid syntax in oracle.

Update foo set somefield=NULL where somefield >9;

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

regards, tom lane

On Mon, 15 Sep 2003 19:34:48 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:

Update foo set somefield=NULL where somefield >9;

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

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

On 15 Sep 2003 at 11:17, Tom Lane wrote:

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:

Following seems to be the valid syntax in oracle.

Update foo set somefield=NULL where somefield >9;

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

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.

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

Manfred Koizar wrote: