By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 2,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Updates with NULL

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

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

Nov 11 '05 #2

P: n/a
"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

Nov 11 '05 #3

P: n/a
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

Nov 11 '05 #4

P: n/a
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

Nov 11 '05 #5

P: n/a
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

Nov 11 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.