Source language: Translate to:

Sql Update field

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Sql Update field

Postby BRobinsonS » Mon Dec 12, 2016 7:16 pm

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?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql Update field

Postby Gaev » Tue Dec 13, 2016 8:28 am

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'.
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql Update field

Postby BRobinsonS » Tue Dec 13, 2016 8:44 am

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sql Update field

Postby Gaev » Tue Dec 13, 2016 2:15 pm

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 ;
User avatar
Gaev
 
Posts: 3736
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sql Update field

Postby BRobinsonS » Wed Dec 14, 2016 1:58 pm

Gaev,
Code: Select all
WHERE ... IS NULL;

was the key.
Thanks !
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 340
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest