MySQL Insert Where query

582 Asked by KirstyDeller in Data Science , Asked on Jul 15, 2021

What's wrong with this query:

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

It works without the WHERE clause. I've seemed to have forgotten my SQL...

Answered by Diana Campbell

MySQL INSERT Syntax does not support the insert where clause so your query as it stands will fail. Assuming your id column is unique or primary key:

If you want to insert a new row with ID 1 then you use the below query:

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

But, If want to change the weight/desiredWeight values for an existing row with ID 1 then, use the below query:

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

If you are trying to use INSERT .. ON DUPLICATE KEY syntax then, do like this:

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

Or do this:

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

MySQL Insert Where query

Your Answer