MySQL Insert Where query

2018-12-31 09:37发布

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..

标签: sql mysql
24条回答
妖精总统
2楼-- · 2018-12-31 09:58

correct syntax for mysql insert into statement using post method is:

$sql="insert into ttable(username,password) values('$_POST[username]','$_POST[password]')";
查看更多
零度萤火
3楼-- · 2018-12-31 10:00

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
    
查看更多
看风景的人
4楼-- · 2018-12-31 10:00

its totall wrong. INSERT QUERY does not have a WHERE clause, Only UPDATE QUERY has it. If you want to add data Where id = 1 then your Query will be

UPDATE Users SET weight=160, desiredWeight= 145 WHERE id = 1;
查看更多
冷夜・残月
5楼-- · 2018-12-31 10:02

You can't use INSERT and WHERE together. You can use UPDATE clause for add value to particular column in particular field like below code;

UPDATE Users
SET weight='160',desiredWeight ='145'  
WHERE id =1
查看更多
牵手、夕阳
6楼-- · 2018-12-31 10:04

After WHERE clause you put a condition, and it is used for either fetching data or for updating a row. When you are inserting data, it is assumed that the row does not exist.

So, the question is, is there any row whose id is 1? if so, use MySQL UPDATE, else use MySQL INSERT.

查看更多
只靠听说
7楼-- · 2018-12-31 10:04

If you are specifying a particular record no for inserting data its better to use UPDATE statement instead of INSERT statement.

This type of query you have written in the question is like a dummy query.

Your Query is :-

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

Here , you are specifying the id=1 , so better you use UPDATE statement to update the existing record.It is not recommended to use WHERE clause in case of INSERT.You should use UPDATE .

Now Using Update Query :-

UPDATE Users SET weight=160,desiredWeight=145 WHERE id=1;
查看更多
登录 后发表回答