MySQL Insert Where query

Question

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 forgot my SQL..

Answer 1

i dont think that we can use where clause in insert statement

Answer 2

I think your best option is use REPLACE instead INSERT

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

Answer 3

INSERT INTO Users(weight, desiredWeight )
SELECT '$userWeight', '$userDesiredWeight'  
FROM (select 1 a ) dummy
WHERE '$userWeight' != '' AND '$userDesiredWeight'!='';

Answer 4

DO READ THIS AS WELL

It doesn't make sense... even literally

INSERT means add a new row and when you say WHERE you define which row are you talking about in the SQL.

So adding a new row is not possible with a condition on an existing row.

You have to choose from the following:

A. Use UPDATE instead of INSERT

B. Use INSERT and remove WHERE clause ( I am just saying it...) or if you are real bound to use INSERT and WHERE in a single statement it can be done only via INSERT..SELECT clause...

INSERT INTO Users( weight, desiredWeight ) 
SELECT FROM Users WHERE id = 1;

But this serves an entirely different purpose and if you have defined id as Primary Key this insert will be failure, otherwise a new row will be inserted with id = 1.

Answer 5

Insert into = Adding rows to a table

Upate = update specific rows.

What would the where clause describe in your insert? It doesn't have anything to match, the row doesn't exist (yet)...

Answer 6

It depends on the situation INSERT can actually have a where clause.

For example if you are matching values from a form.

Consider INSERT INTO Users(name,email,weight, desiredWeight) VALUES (fred,[e-mail],160,145) WHERE name != fred AND email != [e-mail]

Makes sense doesn't it?