Page 1 of 1

Sql Update field

PostPosted: Mon Dec 12, 2016 7:16 pm
by 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?

Re: Sql Update field

PostPosted: Tue Dec 13, 2016 8:28 am
by 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'.

Re: Sql Update field

PostPosted: Tue Dec 13, 2016 8:44 am
by 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.

Re: Sql Update field

PostPosted: Tue Dec 13, 2016 2:15 pm
by 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 ;

Re: Sql Update field

PostPosted: Wed Dec 14, 2016 1:58 pm
by BRobinsonS
Gaev,
Code: Select all
WHERE ... IS NULL;

was the key.
Thanks !