INSERT ... VALUES with a WHERE clause gives a syntax error
ERROR 1064 (42000): ... near 'WHERE' at line 1Short answer
INSERT ... VALUES has no WHERE clause in MySQL, so the parser stops at the word WHERE. Pick the statement that matches your intent: INSERT ... SELECT to insert only when a condition holds, UPDATE ... WHERE to change a row that already exists, or ON DUPLICATE KEY UPDATE for insert-or-update.
Fix it fast
1Find out which cause you have
Decide what the WHERE was supposed to do. Is the row new, or does it already exist? Look at what the table holds for that key.
SELECT * FROM users WHERE id = 7;2Match what you see, then jump to its fix
- 1
The statement reads INSERT INTO t (a, b) VALUES (1, 2) WHERE ...You want to insert a row only when a condition is trueFix - 2
The WHERE names the row's key, such as WHERE id = 7The row already exists and you meant to change itFix - 3
The WHERE tests whether the key existsYou want insert-or-update on a keyFix
- 1
What does the error look like?
ERROR 1064 (42000): ... near 'WHERE' at line 1
Cause 1 of 3
You want to insert a row only when a condition is true
1Check
The INSERT ... VALUES grammar ends with the value list and optional ON DUPLICATE KEY UPDATE. There is no place for WHERE.
SELECT COUNT(*) FROM users WHERE email = 'a@example.com';2Fix
Use INSERT ... SELECT. The WHERE belongs to the SELECT that produces the rows. The manual says you cannot select from the same table in a subquery of the INSERT, so for the not-exists pattern use a unique key instead (third cause).
INSERT INTO archive (id, name)
SELECT id, name FROM users WHERE created_at < '2025-01-01';3Verify
The row count matches the SELECT run on its own.
SELECT COUNT(*) FROM archive;Confirmed by dev.mysql.com
Cause 2 of 3
The row already exists and you meant to change it
1Check
If the SELECT above returns a row, an INSERT is the wrong statement.
SELECT id, name FROM users WHERE id = 7;2Fix
Use UPDATE. Assignments in SET are separated by commas, and the WHERE chooses the rows.
UPDATE users SET name = 'Sara', city = 'Leeds' WHERE id = 7;3Verify
The row now shows the new values.
SELECT name, city FROM users WHERE id = 7;Confirmed by dev.mysql.com
Cause 3 of 3
You want insert-or-update on a key
1Check
This needs a PRIMARY KEY or UNIQUE index on the column that identifies the row.
SHOW INDEX FROM users;2Fix
Insert, and let MySQL update the old row when the key already exists.
INSERT INTO users (id, name) VALUES (7, 'Sara') AS new
ON DUPLICATE KEY UPDATE name = new.name;3Verify
Run it twice; the second run updates instead of failing with a duplicate key error.
SELECT id, name FROM users WHERE id = 7;Confirmed by dev.mysql.com
How do I stop it coming back?
- Add a UNIQUE index for every value that must not repeat, so MySQL enforces it rather than your WHERE clause.
- Test the SELECT part of an INSERT ... SELECT on its own first.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix