Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT ... VALUES with a WHERE clause gives a syntax error

ErrorERROR 1064 (42000): ... near 'WHERE' at line 1

Short 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.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The statement reads INSERT INTO t (a, b) VALUES (1, 2) WHERE ...You want to insert a row only when a condition is trueFix
    2. 2The WHERE names the row's key, such as WHERE id = 7The row already exists and you meant to change itFix
    3. 3The WHERE tests whether the key existsYou want insert-or-update on a keyFix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.