Description:
I have a decimal(4,2) field in my table. If I do SELECT * from surveys AND try and edit it (update) in the "MySql Query Browser" it throws a 1064 error:
"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' WHERE `ssid`='1'' at line 1"
HOWEVER - if I open a new tab and write out the update script it works fine ie
UPDATE surveys SET Price = 10.00 where ssID=1
Here are the details of my table called "Surveys":
(
`ssid` int(10) unsigned NOT NULL auto_increment,
`surveyorID` int(10) unsigned NOT NULL,
`surveyDate` date NOT NULL,
`Price` decimal(4,2) NOT NULL default '0.00',
`filePath` varchar(255) NOT NULL,
`flatpos` varchar(45) default NULL,
`housename` varchar(45) default NULL,
`roadNo` varchar(45) default NULL,
`thoroughfare` varchar(45) NOT NULL,
`locality` varchar(45) default NULL,
`post_town` varchar(45) NOT NULL,
`postcode` varchar(10) NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`ssid`)
) ENGINE=MyISAM DEFAULT
I am using MySql Server Version: '5.0.15-nt' and Mysql Query Browser 1.24
How to repeat:
Use the table details in the description to define the table, then load some example data:
INSERT INTO `surveys` (`surveyorID`,`surveyDate`,`Price`,`filePath`,`flatpos`,`housename`,`roadNo`,`thoroughfare`,`locality`,`post_town`,`postcode`,`active`) VALUES
(1,'2008-01-15','50.00','/singlesurvey/surveys/sample.pdf',NULL,'','123','Main Street',NULL,'Largs','KA30 8AD',1),
(1,'2007-12-01','10.00','/singlesurvey/surveys/sample.pdf','3f2',NULL,'5','Dalkeith Road',NULL,'Edinburgh','EH16 5BW',1);
Open up the MySql Query Browser, click on the EDIT button at the bottom of the screen and alter the Price to be something else ... say '15.99' and then click SAVE CHANGES (next to the edit button). The edited field goes red and you get an error 1064 message.
TRY opening a new tab and then run:
UPDATE surveys SET Price = 15.99 where ssID=1
The field should update without any problems.
