Why Does My MySQL INSERT Query Fail with a WHERE Clause?

MySQL INSERT query conflicts with WHERE clause

When trying to insert data into a MySQL database using a query similar to:

<code class="language-sql">INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;</code>

You may receive an error message if a WHERE clause is present. This is because MySQL's INSERT syntax does not support a WHERE clause.

New line

If the intention is to create a new row with a specified ID, the correct query would look like this:

<code class="language-sql">INSERT INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);</code>

Update existing row

To modify the value in an existing row, the UPDATE statement should be used:

<code class="language-sql">UPDATE Users SET weight = 160, desiredWeight = 145 WHERE id = 1;</code>

INSERT ... ON DUPLICATE KEY

Alternatively, you can use the INSERT ... ON DUPLICATE KEY syntax to insert or update rows based on a unique or primary key:

<code class="language-sql">INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145</code>

Use auto-increment column

If the id column in the table is set to auto-increment, you can omit it from the INSERT query and let MySQL handle the increment:

<code class="language-sql">INSERT INTO Users SET weight=160, desiredWeight=145</code>

The above is the detailed content of Why Does My MySQL INSERT Query Fail with a WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!