MySQL INSERT INTO failing at WHERE clause

Question

Ok, I'm stumped on this one:

mysql> INSERT INTO item (col1) VALUES ('testing') WHERE item_name = 'server1';

ERROR 1064 (42000): 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 item_name = 'server1'' at line 1

Here's the table desc (slightly sanitized):

mysql> desc item;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment | 
| item_name    | varchar(128) | YES  |     |         |                | 
| active       | int(11)      | NO   |     | 0       |                | 
| col1         | varchar(512) | YES  |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+

]I've tried some variations on the INSERT, but I've gotten nowhere. Look forward to someone revealing whatever obvious thing I'm missing!

Running: mysql Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1 (I know it's older, but I can't upgrade this box at the moment).

Hope this answer will helpful: http://stackoverflow.com/a/485062/2451726 — Arulkumar, Apr 07 '15 at 06:04
INSERT INTO not support WHERE This http://stackoverflow.com/questions/485039/mysql-insert-where-query will help. — starplateena, Apr 07 '15 at 06:07
instead of insert query you use update query in where condition — Saty, Apr 07 '15 at 06:10
Thanks everyone! I totally spaced on the UPDATE and that's exactly what I needed. Appreciate the help! — n1reu, Apr 07 '15 at 18:17

score 2 · Accepted Answer · answered Apr 07 '15 at 06:10

2

If you're looking to update an existing row, it's this:

UPDATE item
  SET col1 = 'testing'
  WHERE item_name = 'server1';

answered Apr 07 '15 at 06:10

pala_

8,901
1
15
32

score 1 · Answer 2 · answered Apr 07 '15 at 06:08

You're not using the INSERTstatement correctly, if you use VALUES clause you can't apply a WHERE condition on it.

Here is the same query with the appropriate syntax:

INSERT INTO item (col1)
SELECT 'testing'
FROM yourTable
WHERE item_name = 'server1';

Hope this will help you.

MySQL INSERT INTO failing at WHERE clause

2 Answers2