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 do not believe the insert has a WHERE clause.

Answer 2

You use the WHERE clause for UPDATE queries. When you INSERT, you are assuming that the row doesn't exist.

In MySQL, if you want to INSERT or UPDATE, you can use the REPLACE query with a WHERE clause. If the WHERE doesn't exist, it INSERTS, otherwise it UPDATES.

EDIT

I think that Bill Karwin's point is important enough to pull up out of the comments and make it very obvious. Thanks Bill, it has been too long since I have worked with MySQL, I remembered that I had issues with REPLACE, but I forgot what they were. I should have looked it up.

That's not how MySQL's REPLACE works. It does a DELETE (which may be a no-op if the row does not exist), followed by an INSERT. Think of the consequences vis. triggers and foreign key dependencies. Instead, use INSERT...ON DUPLICATE KEY UPDATE.

Answer 3

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 4

You can't combine a WHERE clause with a VALUES clause. You have two options as far as I am aware-

1. INSERT specifying values

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

2. INSERT using a SELECT statement

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

Answer 5

Ah.. Update is what I was looking for. Thanks! I really need to brush up on my SQL.

Answer 6

If you're trying to insert a new row with ID 1 you should be using:

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

If you're trying to change the weight/desiredWeight values for an existing row with ID 1 you should be using:

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