How to Correctly Insert or Update Data in MySQL Using INSERT and WHERE Clauses?

MySQL data insertion and update: Correct usage of INSERT and WHERE clauses

MySQL's INSERT statement does not support the WHERE clause. Therefore, the following query:

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

will fail.

Insert new line

If you are trying to insert a new row with ID 1, use the following query:

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

Update existing row

If you want to change the weight/desiredWeight value of an existing row with ID 1, use the UPDATE statement:

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

INSERT ... ON DUPLICATE KEY Grammar

You can also use the INSERT ... ON DUPLICATE KEY syntax:

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

or:

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

Self-added column

If the id column is an auto-increment column, you can completely omit the INSERT column in the query: id

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

MySQL will automatically increment the

value. id

The above is the detailed content of How to Correctly Insert or Update Data in MySQL Using INSERT and WHERE Clauses?. For more information, please follow other related articles on the PHP Chinese website!