How can I update a tinyint value to be NULL?

[XgrinderX]

I am trying to update a value in my table. I have a field called tiPhone and it is a tinyint. I want to be able to update it to NULL but when I try to do it, instead it updates to 0.

UPDATE tblUser SET tiPhone = 'NULL' WHERE iUserID = 1

The above query will set the tiPhone value to 0 every time, but I want it to be NULL. How can I accomplish this?

-Greg

 

[XgrinderX]

Jeesh.

Nevermind, I figured it out. It wanted NULL without the single quotes. For integer field it wants the quotes but apparently for tinyint it does not.

 

[r937]

for integer field it wants the quotes" is not correct

you should never use quotes around numeric literals



r937.com | rudy.ca

 

[XgrinderX]

if i don't it gives an error. my first attempt was with no quotes on any of them. then i tried with the single quotes on all of them. after some trial and error, i landed on this.

unless you know something i don't know - it's working fine now with the 'NULL' for ints and just NULL for tinyints. not sure why it works that way, but it does.

 

[r937]

show your CREATE TABLE statement and i'll explain why



r937.com | rudy.ca

 

[XgrinderX]

Pulled this out of the table properties in Navicat. Hope it has what you need. Does it really matter as long as it works for me?

CREATE TABLE `tbluser` (
`iUserID` int(11) NOT NULL auto_increment,
`iGroupID` int(11) default NULL,
`sUserName` varchar(16) default NULL,
`sPassword` varchar(16) default NULL,
`sFirstName1` varchar(50) default NULL,
`sLastName1` varchar(50) default NULL,
`sFirstName2` varchar(50) default NULL,
`sLastName2` varchar(50) default NULL,
`sAddress1` varchar(150) default NULL,
`sAddress2` varchar(150) default NULL,
`sCity` varchar(100) default NULL,
`sState` char(2) default NULL,
`sZip` varchar(10) default NULL,
`sEmail1` varchar(100) default NULL,
`tiEmail1` tinyint(4) default NULL,
`sEmail2` varchar(100) default NULL,
`tiEmail2` tinyint(4) default NULL,
`sEmail3` varchar(100) default NULL,
`tiEmail3` tinyint(4) default NULL,
`sEmail4` varchar(100) default NULL,
`tiEmail4` tinyint(4) default NULL,
`sPhone1` varchar(12) default NULL,
`iPhone1ID` int(11) default NULL,
`tiPhone1` tinyint(4) default NULL,
`sPhone2` varchar(12) default NULL,
`iPhone2ID` int(11) default NULL,
`tiPhone2` tinyint(4) default NULL,
`sPhone3` varchar(12) default NULL,
`iPhone3ID` int(11) default NULL,
`tiPhone3` tinyint(4) default NULL,
`sPhone4` varchar(12) default NULL,
`iPhone4ID` int(11) default NULL,
`tiPhone4` tinyint(4) default NULL,
`sPhone5` varchar(12) default NULL,
`iPhone5ID` int(11) default NULL,
`tiPhone5` tinyint(4) default NULL,
`nSSN1` int(11) default NULL,
`nSSN2` int(11) default NULL,
`dtJoinDate` datetime default NULL,
`dtLastLogin` datetime default NULL,
`sWebMailUser` varchar(15) default NULL,
`sWebMailPass` varchar(15) default NULL,
`sUserNotes` varchar(250) default NULL,
PRIMARY KEY (`iUserID`),
KEY `sUserName` (`sUserName`)
) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB; InnoDB free: 4096 kB; InnoDB free: 409'

 

[r937]

when you insert a character string like 'fred' or 'NULL' (both of which are not valid integers) into an integer column that has been defined as default NULL, then you will get the default

it's as simple as that

does it really matter? well, i think it does -- you will have nothing but frustration if you ever use another database besides mysql, which is very "forgiving" (to put it kindly)

r937.com | rudy.ca