Re: [SQL] Why wont this update work? - Mailing list pgsql-sql

<tt>Robert Chalmers ha scritto:</tt><blockquote type="CITE"><tt>Hi,</tt><br /><tt>trying to get this to work with
pgsql,but it refuses to do anything...</tt><tt></tt><p><tt>   the table is called catalog. The field is called language
andis a</tt><br /><tt>varchar(3) field.</tt><br /><tt>   81 of the records have the letter 'E' in the language field. i
wantto</tt><br /><tt>put 'NoD' in the rest.</tt><tt></tt><p><tt>  UPDATE catalog</tt><br /><tt>     SET language =
'NoD'</tt><br/><tt>     WHERE NOT language = 'E';</tt><br /><tt></tt> </blockquote><tt>I think you have a NULL
problem:</tt><tt></tt><p><tt>Thisquery doesn't work probably because the language column is always 'E' or NULL and NULL
isan unknown value,</tt><br /><tt>therefore if you compare a NULL value with 'E'  it is always FALSE.</tt><br
/><tt></tt> <blockquote type="CITE"><tt></tt>  <br /><tt>nor does</tt><tt></tt><p><tt>  UPDATE catalog</tt><br
/><tt>    SET language = 'NoD'</tt><br /><tt>     WHERE language = 'NULL';    ........ or NULL and so
on.</tt><tt></tt><p><tt>Ican change the 81 letter 'E's to anything I like, but can not UPDATE the</tt><br /><tt>blank
fields..</tt><tt></tt><p><tt>cananyone tell me what I am missing here please?</tt><br /><tt></tt> </blockquote><tt>This
shouldwork:</tt><tt></tt><p><tt>UPDATE catalog SET language = 'NoD'</tt><br /><tt>WHERE language IS NULL        --this
forevery NULL value</tt><br /><tt>OR language <> 'E';           --and this one to everey value different from
NULL</tt><tt></tt><p><tt>Example:</tt><tt></tt><p><tt>createtable catalog(id int, language varchar(3));</tt><br
/><tt>CREATE</tt><br/><tt>insert into catalog values(1,'E');</tt><br /><tt>INSERT 150666 1</tt><br /><tt>insert into
catalogvalues(2,'E');</tt><br /><tt>INSERT 150667 1</tt><br /><tt>insert into catalog values(3,'z');</tt><br
/><tt>INSERT150668 1</tt><br /><tt>insert into catalog values(4);</tt><br /><tt>INSERT 150669 1</tt><br /><tt>insert
intocatalog values(5);</tt><br /><tt>INSERT 150670 1</tt><br /><tt>insert into catalog values(6);</tt><br /><tt>INSERT
1506711</tt><br /><tt>select * from catalog;</tt><br /><tt>id|language</tt><br /><tt>--+--------</tt><br
/><tt> 1|E</tt><br/><tt> 2|E</tt><br /><tt> 3|z</tt><br /><tt> 4|</tt><br /><tt> 5|</tt><br /><tt> 6|</tt><br /><tt>(6
rows)</tt><tt></tt><p><tt>select* from catalog where language = 'E';</tt><br /><tt>id|language</tt><br
/><tt>--+--------</tt><br/><tt> 1|E</tt><br /><tt> 2|E</tt><br /><tt>(2 rows)</tt><tt></tt><p><tt>select * from catalog
wherelanguage <> 'E';</tt><br /><tt>id|language</tt><br /><tt>--+--------</tt><br /><tt> 3|z</tt><br /><tt>(1
row)</tt><tt></tt><p><tt>select* from catalog where language <> 'E' or language is null;</tt><br
/><tt>id|language</tt><br/><tt>--+--------</tt><br /><tt> 3|z</tt><br /><tt> 4|</tt><br /><tt> 5|</tt><br
/><tt> 6|</tt><br/><tt>(4 rows)</tt><blockquote type="CITE">  <br />---  <a
href="http://4qir.quantum-radio.net.au">http://4qir.quantum-radio.net.au</a>- Where Only The Dedicated Survive! <br
/>4QIRQuantum Radio. Bringing you the original sounds of AFVN, with some of <br />the <br />original DJ's - back on air
-and supporting not only the original Vets, in <br />comprehensive AFVN Archives, but putting together segments for the
fighting<br />men and <br />women of todays services all around the world. World Music, for people of <br />the
world.</blockquote>______________________________________________________________ <br />PostgreSQL 6.5.0 on
i586-pc-linux-gnu,compiled by gcc 2.7.2.3 <br />^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ <br
/>José