Want to INSERT into one column that I have added

[rocky48]

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.

[cyberRobot]

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

[Muddy_Funster]

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.

[rocky48]

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?

[Muddy_Funster]

you don't use brackets for SET values:

 

UPDATE `ctryvisits15`SET `Qtr`= 1 WHERE ẀNo`< 14

[Barand]

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 |
+------+

[Psycho]

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