pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Why wont this update work?
5+ messages / 5 participants
[nested] [flat]

* Why wont this update work?
@ 1999-05-19 04:49 Robert Chalmers <robert@chalmers.com.au>
  1999-05-19 05:08 ` Re: [SQL] Why wont this update work? Chris Bitmead <chris.bitmead@bigfoot.com>
  1999-05-19 13:08 ` Re: [SQL] Why wont this update work? Jason Earl <jearl@box100.com>
  1999-05-19 13:53 ` Re: [SQL] Why wont this update work? Tom Lane <tgl@sss.pgh.pa.us>
  1999-05-19 14:19 ` Re: [SQL] Why wont this update work? José Soares <jose@sferacarta.com>
  0 siblings, 4 replies; 5+ messages in thread

From: Robert Chalmers @ 1999-05-19 04:49 UTC (permalink / raw)
  To: pgsql-sql

Hi,
trying to get this to work with pgsql, but it refuses to do anything...

   the table is called catalog. The field is called language and is a
varchar(3) field.
   81 of the records have the letter 'E' in the language field. i want to
put 'NoD' in the rest.

  UPDATE catalog
     SET language = 'NoD'
     WHERE NOT language = 'E';

nor does

  UPDATE catalog
     SET language = 'NoD'
     WHERE language = 'NULL';    ........ or NULL and so on.

I can change the 81 letter 'E's to anything I like, but can not UPDATE the
blank fields..

can anyone tell me what I am missing here please?

Thanks
Bob


---  http://4qir.quantum-radio.net.au - Where Only The Dedicated Survive!
4QIR Quantum Radio. Bringing you the original sounds of AFVN, with some of
the
original DJ's - back on air - and supporting not only the original Vets, in
comprehensive AFVN Archives, but putting together segments for the fighting
men and
women of todays services all around the world. World Music, for people of
the world.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Why wont this update work?
  1999-05-19 04:49 Why wont this update work? Robert Chalmers <robert@chalmers.com.au>
@ 1999-05-19 05:08 ` Chris Bitmead <chris.bitmead@bigfoot.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Chris Bitmead @ 1999-05-19 05:08 UTC (permalink / raw)
  To: ; +Cc: pgsql-sql


Well "language" is an SQL reserved word. In Postgres it is a
non-reserved key word, so it's probably not the problem but it may be
worth seeing if changing the field name helps.


Robert Chalmers wrote:
> 
> Hi,
> trying to get this to work with pgsql, but it refuses to do anything...
> 
>    the table is called catalog. The field is called language and is a
> varchar(3) field.
>    81 of the records have the letter 'E' in the language field. i want to
> put 'NoD' in the rest.
> 
>   UPDATE catalog
>      SET language = 'NoD'
>      WHERE NOT language = 'E';
> 
> nor does
> 
>   UPDATE catalog
>      SET language = 'NoD'
>      WHERE language = 'NULL';    ........ or NULL and so on.
> 
> I can change the 81 letter 'E's to anything I like, but can not UPDATE the
> blank fields..
> 
> can anyone tell me what I am missing here please?
> 
> Thanks
> Bob
> 
> ---  http://4qir.quantum-radio.net.au - Where Only The Dedicated Survive!
> 4QIR Quantum Radio. Bringing you the original sounds of AFVN, with some of
> the
> original DJ's - back on air - and supporting not only the original Vets, in
> comprehensive AFVN Archives, but putting together segments for the fighting
> men and
> women of todays services all around the world. World Music, for people of
> the world.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Why wont this update work?
  1999-05-19 04:49 Why wont this update work? Robert Chalmers <robert@chalmers.com.au>
@ 1999-05-19 13:08 ` Jason Earl <jearl@box100.com>
  3 siblings, 0 replies; 5+ messages in thread

From: Jason Earl @ 1999-05-19 13:08 UTC (permalink / raw)
  To: robert@chalmers.com.au; +Cc: pgsql-sql


UPDATE catalog
       SET language = 'NoD'
       WHERE language IS NULL;

Hope that helps,
Jason


   Hi,
   trying to get this to work with pgsql, but it refuses to do anything...

      the table is called catalog. The field is called language and is a
   varchar(3) field.
      81 of the records have the letter 'E' in the language field. i want to
   put 'NoD' in the rest.

     UPDATE catalog
	SET language = 'NoD'
	WHERE NOT language = 'E';

   nor does

     UPDATE catalog
	SET language = 'NoD'
	WHERE language = 'NULL';    ........ or NULL and so on.

   I can change the 81 letter 'E's to anything I like, but can not UPDATE the
   blank fields..

   can anyone tell me what I am missing here please?

   Thanks
   Bob


   ---  http://4qir.quantum-radio.net.au - Where Only The Dedicated Survive!
   4QIR Quantum Radio. Bringing you the original sounds of AFVN, with some of
   the
   original DJ's - back on air - and supporting not only the original Vets, in
   comprehensive AFVN Archives, but putting together segments for the fighting
   men and
   women of todays services all around the world. World Music, for people of
   the world.







^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Why wont this update work?
  1999-05-19 04:49 Why wont this update work? Robert Chalmers <robert@chalmers.com.au>
@ 1999-05-19 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 5+ messages in thread

From: Tom Lane @ 1999-05-19 13:53 UTC (permalink / raw)
  To: Robert Chalmers <robert@chalmers.com.au>; +Cc: pgsql-sql

"Robert Chalmers" <robert@chalmers.com.au> writes:
>   UPDATE catalog
>      SET language = 'NoD'
>      WHERE language = 'NULL';    ........ or NULL and so on.
> [ doesn't work ]

WHERE language IS NULL is the SQL-standard-approved way of testing
for NULL fields.  (There's also IS NOT NULL if you need that.)

If you have a sufficiently recent version of Postgres, it will also
accept WHERE language = NULL (no quotes), but that's a nonstandard
extension.  (We only put it in because we had to for reasons of
compatibility with Microsoft applications.)

>   UPDATE catalog
>      SET language = 'NoD'
>      WHERE NOT language = 'E';

This approach cannot work for finding nulls, because in expressions
null essentially means "don't know" ... and that means it propagates.
Is null equal to 'E'?  The answer is "I don't know" --- another null.
Feed that null to the NOT operator, and out comes another null.  The
buck stops at the WHERE clause, which is looking for a 'true' result
... but null isn't true either, so the tuple doesn't get selected.

In short, no expression involving ordinary comparison and logic
operators can produce anything but null when fed a null input.
And that means you can't select tuples containing nulls with such
an expression.

This is why the standard decrees a special syntax for testing for
null --- it's a very special operation.  Microsoft's database code
is in fact violating the standard by accepting field = NULL as meaning
field IS NULL, because by rights field = NULL should always produce
a null result (whether the field contains null or not!).

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] Why wont this update work?
  1999-05-19 04:49 Why wont this update work? Robert Chalmers <robert@chalmers.com.au>
@ 1999-05-19 14:19 ` José Soares <jose@sferacarta.com>
  3 siblings, 0 replies; 5+ messages in thread

From: José Soares @ 1999-05-19 14:19 UTC (permalink / raw)
  To: Robert Chalmers <robert@chalmers.com.au>; +Cc: pgsql-sql

Robert Chalmers ha scritto:

> Hi,
> trying to get this to work with pgsql, but it refuses to do anything...
>
>    the table is called catalog. The field is called language and is a
> varchar(3) field.
>    81 of the records have the letter 'E' in the language field. i want to
> put 'NoD' in the rest.
>
>   UPDATE catalog
>      SET language = 'NoD'
>      WHERE NOT language = 'E';
>

I think you have a NULL problem:

This query doesn't work probably because the language column is always 'E' or
NULL and NULL is an unknown value,
therefore if you compare a NULL value with 'E'  it is always FALSE.


>
> nor does
>
>   UPDATE catalog
>      SET language = 'NoD'
>      WHERE language = 'NULL';    ........ or NULL and so on.
>
> I can change the 81 letter 'E's to anything I like, but can not UPDATE the
> blank fields..
>
> can anyone tell me what I am missing here please?
>

This should work:

UPDATE catalog SET language = 'NoD'
WHERE language IS NULL        --this for every NULL value
OR language <> 'E';           --and this one to everey value different from
NULL

Example:

create table catalog(id int, language varchar(3));
CREATE
insert into catalog values(1,'E');
INSERT 150666 1
insert into catalog values(2,'E');
INSERT 150667 1
insert into catalog values(3,'z');
INSERT 150668 1
insert into catalog values(4);
INSERT 150669 1
insert into catalog values(5);
INSERT 150670 1
insert into catalog values(6);
INSERT 150671 1
select * from catalog;
id|language
--+--------
 1|E
 2|E
 3|z
 4|
 5|
 6|
(6 rows)

select * from catalog where language = 'E';
id|language
--+--------
 1|E
 2|E
(2 rows)

select * from catalog where language <> 'E';
id|language
--+--------
 3|z
(1 row)

select * from catalog where language <> 'E' or language is null;
id|language
--+--------
 3|z
 4|
 5|
 6|
(4 rows)

>
> ---  http://4qir.quantum-radio.net.au - Where Only The Dedicated Survive!
> 4QIR Quantum Radio. Bringing you the original sounds of AFVN, with some of
> the
> original DJ's - back on air - and supporting not only the original Vets, in
> comprehensive AFVN Archives, but putting together segments for the fighting
> men and
> women of todays services all around the world. World Music, for people of
> the world.

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
José

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1999-05-19 14:19 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-19 04:49 Why wont this update work? Robert Chalmers <robert@chalmers.com.au>
1999-05-19 05:08 ` Chris Bitmead <chris.bitmead@bigfoot.com>
1999-05-19 13:08 ` Jason Earl <jearl@box100.com>
1999-05-19 13:53 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-19 14:19 ` José Soares <jose@sferacarta.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox