rocky48 Posted June 9, 2015 Share Posted June 9, 2015 I am trying to INSERT data into a column that I have added to the table, but I keep getting a syntax error. Here is the line: INSERT INTO `ctryvisits15`(`Qtr`) VALUES (2) WHERE `WNo` <27 The message is : #1064 - 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 '2) WHERE `WNo` <27' at line 1 I have tried every thing I can think of but can't see where I am going wrong. I am using phpMyAdmin to update my data. Quote Link to comment Share on other sites More sharing options...
cyberRobot Posted June 9, 2015 Share Posted June 9, 2015 As far as I'm aware, the WHERE clause can't be used with INSERT. More information on the syntax can be found here: http://dev.mysql.com/doc/refman/5.6/en/insert.html Are you trying to update a set of existing rows? If so, have you looked into using UPDATE? https://dev.mysql.com/doc/refman/5.0/en/update.html Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted June 11, 2015 Share Posted June 11, 2015 cyberRobot is of course correct. INSERT creates a completely new record, thus WHERE can not be used (there is no existing point of reference for a new record to use in this way). WHERE can only be used when interacting with existing data (using SELECT, or the suggested UPDATE for example). It is used to make conditions which reference the existing records in the table so that only the desired records are affected. If you give us a bit more background on what you are doing we can advise further. Quote Link to comment Share on other sites More sharing options...
rocky48 Posted June 11, 2015 Author Share Posted June 11, 2015 Tried UPDATE but that gives the same result: UPDATE `ctryvisits15` SET `Qtr`=[1] WHERE `WNo`<14; Is it because I am only trying to update just 1 column in the table? Quote Link to comment Share on other sites More sharing options...
Solution Muddy_Funster Posted June 11, 2015 Solution Share Posted June 11, 2015 you don't use brackets for SET values: UPDATE `ctryvisits15`SET `Qtr`= 1 WHERE ẀNo`< 14 Quote Link to comment Share on other sites More sharing options...
Barand Posted June 11, 2015 Share Posted June 11, 2015 Out of curiosity, do your `ctryvisits15` table rows contain a DATE column for the date of the visit? if so, the week number and quarter columns are redundant. You can derive both those values direct from the date. mysql> SELECT WEEK('2015-06-11') as wkno; +------+ | wkno | +------+ | 23 | +------+ mysql> SELECT QUARTER('2015-06-11') as qtr; +------+ | qtr | +------+ | 2 | +------+ Quote Link to comment Share on other sites More sharing options...
Psycho Posted June 11, 2015 Share Posted June 11, 2015 UPDATE `ctryvisits15`SET `Qtr`= 1 WHERE ẀNo`< 14 FYI: It appears the back tick and the W got combined into a single character with a dialectic! That should be UPDATE `ctryvisits15`SET `Qtr`= 1 WHERE `WNo`< 14 1 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.