Postgres Pro
Downloads
Home   >   mailing lists

Thread: Updates with NULL

Updates with NULL

From
"Shridhar Daithankar"
Date:
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?

Re: Updates with NULL

From
Stephan Szabo
Date:
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

Re: Updates with NULL

From
Tom Lane
Date:
"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

Re: Updates with NULL

From
Manfred Koizar
Date:
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

Re: Updates with NULL

From
"Shridhar Daithankar"
Date:
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.

Re: Updates with NULL

From
Dennis Gearon
Date:
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"
><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
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>