Updates with NULL

[Shridhar Daithankar]

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

[Stephan Szabo]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Shridhar Daithankar" <sh*****************@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

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

[Manfred Koizar]

On Mon, 15 Sep 2003 19:34:48 +0530, "Shridhar Daithankar"
<sh*****************@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

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

[Shridhar Daithankar]

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

"Shridhar Daithankar" <sh*****************@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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Dennis Gearon]

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

Manfred Koizar wrote:

On Mon, 15 Sep 2003 19:34:48 +0530, "Shridhar Daithankar"
<sh*****************@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

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

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