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:

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

will fail.

Insert new line

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

INSERT INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

Update existing row

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

UPDATE Users SET weight = 160, desiredWeight = 145 WHERE id = 1;

INSERT ... ON DUPLICATE KEY Grammar

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

INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145;

or:

INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145;

Self-added column

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

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 );

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!