Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA05171 for ; Wed, 19 May 1999 10:21:40 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.8.6/8.8.8/NETTuno 3.2) with ESMTP id QAA14136; Wed, 19 May 1999 16:21:07 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 10k94w-0002N6-00; Wed, 19 May 1999 16:20:22 +0000 Message-ID: <3742C861.4673521A@sferacarta.com> Date: Wed, 19 May 1999 16:19:13 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Robert Chalmers CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Why wont this update work? References: <008d01bea1b2$fc0fd820$1a6001cb@chalmers.com.au> Content-Type: multipart/alternative; boundary="------------5AD2C3F0FE35520C54211295" --------------5AD2C3F0FE35520C54211295 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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é --------------5AD2C3F0FE35520C54211295 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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é --------------5AD2C3F0FE35520C54211295--