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

The simplest way is to use IF to violate your a key constraint. This only works for INSERT IGNORE but will allow you to use constraint in a INSERT.

INSERT INTO Test (id, name) VALUES (IF(1!=0,NULL,1),'Test');

Answer 2

You Should not use where condition in Insert statement. If you want to do, use insert in a update statement and then update a existing record.

Actually can i know why you need a where clause in Insert statement??

Maybe based on the reason I might suggest you a better option.

Answer 3

I am aware that this is a old post but I hope that this will still help somebody, with what I hope is a simple example:

background:

I had a many to many case: the same user is listed multiple times with multiple values and I wanted to Create a new record, hence UPDATE wouldn't make sense in my case and I needed to address a particular user just like I would do using a WHERE clause.

INSERT into MyTable(aUser,aCar)
value(User123,Mini)

By using this construct you actually target a specific user (user123,who has other records) so you don't really need a where clause, I reckon.

the output could be:

aUser   aCar
user123 mini
user123 HisOtherCarThatWasThereBefore

Answer 4

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 5

The right answer to this question will be sth like this:

a). IF want select before insert :

INSERT INTO Users( weight, desiredWeight ) 
  select val1 , val2  from tableXShoulatNotBeUsers
  WHERE somecondition;

b). IF record already exists use update instead of insert:

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

Should be

Update Users set weight=160, desiredWeight=145  WHERE id = 1;

c). If you want to update or insert at the same time

Replace Users set weight=160, desiredWeight=145  WHERE id = 1;

Note):- you should provide values to all fields else missed field in query 
        will be set to null

d). If you want to CLONE a record from SAME table, just remember you cann't select from table to which you are inserting therefore

 create temporary table xtable ( weight int(11), desiredWeight int(11) ;

 insert into xtable (weight, desiredWeight) 
    select weight, desiredWeight from Users where [condition]

 insert into Users (weight, desiredWeight) 
    select weight , desiredWeight from xtable;

I think this pretty covers most of the scenarios

Answer 6

No. As far as I am aware you cannot add the WHERE clause into this query. Maybe I've forgotten my SQL too, because I am not really sure why you need it anyway.