問題描述
這個查詢有什么問題:
INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;
它可以在沒有 WHERE 子句的情況下工作.我似乎忘記了我的 SQL.
It works without the WHERE clause. I've seemed to have forgot my SQL.
推薦答案
MySQL INSERT 語法 不支持 WHERE 子句，因此您的查詢將失敗.假設您的 id 列是唯一的或主鍵:
MySQL INSERT Syntax does not support the WHERE clause so your query as it stands will fail. Assuming your id column is unique or primary key:
如果您嘗試插入 ID 為 1 的新行，您應該使用:
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);
如果您嘗試更改 ID 為 1 的現有行的 weight/desiredWeight 值，您應該使用:
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;
如果你愿意，你也可以像這樣使用 INSERT .. ON DUPLICATE KEY 語法:
If you want you can also use INSERT .. ON DUPLICATE KEY syntax like so:
INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145
或者甚至像這樣:
INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145
同樣重要的是要注意，如果你的 id 列是一個自動增量列，那么你最好從你的 INSERT 中省略它，讓 mysql 像往常一樣增加它.
It's also important to note that if your id column is an autoincrement column then you might as well omit it from your INSERT all together and let mysql increment it as normal.
這篇關于MySQL 插入查詢不適用于 WHERE 子句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！