Sql Update field

[BRobinsonS]

I wanted to update new fields created after other records created.
1. I wanted to remove the Default 'Canada' to a blank which worked using
SQL:

Code: Select all

UPDATE Contacts
SET Country=''
WHERE Country='Canada';

I have some contacts in USA, most Canada.

2. I want the opposite for two fields MagChoice and PrivChoice from Blank to Text using

Code: Select all

UPDATE Contacts
SET MagChoice='English'
WHERE MagChoice='';

No changes were made.
The field are originally empty.
Similar SQL for PrivChoice changing to PrivChoice to 'Yes'.

3. I was able to change all records omitting the WHERE statement, but if MagChoice was 'None' its now 'English'.
And similar PrivChoice was 'No' its now 'Yes'.

I think the WHERE clause doesn't recognize Empty field using '' .
How can I make this work?

[Gaev]

Brian:

Are MagChoice and PriveChoice defined as String/Text fields or Boolean ? ... if Text, what are their lengths ?

Perhaps they contain null values.

If PrivChoice ONLY contains Yes, No and blank/null values, you could select the ones that are not equal 'No' and change them to 'Yes' ... so blank/null AND previous Yes values end up as 'Yes'.

[BRobinsonS]

MagChoice (String 30) and PrivChoice (String10).
They were added after initial creation of Database hence the need to fill in the values.
Therefore, would have Null values. I guess Blank and Null are not the same.

PrivChoice is Yes/Oui or No/Non (Canadian Eh!)

MagChoice is English, English with French Insert or None.

[Gaev]

Brian:

This page ... http://www.w3schools.com/sql/sql_null_values.asp ... shows an example of using NULL in a SELECT statement ....

Code: Select all

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

... no reason you can not use the same in an UPDATE statement e.g. ...

Code: Select all

UPDATE Contacts
SET MagChoice='English'
WHERE MagChoice IS NULL ;

[BRobinsonS]

Gaev,

Code: Select all

WHERE ... IS NULL;

was the key.
Thanks !