MySQL 插入 Where 查询

MySQL Insert Where query

提问by Chad Birch

What's wrong with this query:

这个查询有什么问题：

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

It works without the WHEREclause. I've seemed to have forgot my SQL..

它在没有WHERE子句的情况下工作。我似乎忘记了我的 SQL ..

回答by Chad Birch

MySQL INSERT Syntaxdoes not support the WHERE clause so your query as it stands will fail. Assuming your idcolumn is unique or primary key:

MySQL INSERT 语法不支持 WHERE 子句，因此您的查询将失败。假设您的id列是唯一的或主键：

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

如果您尝试插入 ID 为 1 的新行，您应该使用：

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:

如果您尝试更改 ID 为 1 的现有行的 weight/desiredWeight 值，您应该使用：

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

If you want you can also use INSERT .. ON DUPLICATE KEY syntax like so:

如果你愿意，你也可以使用 INSERT .. ON DUPLICATE KEY 语法，如下所示：

INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

OR even like so:

或者甚至像这样：

INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

It's also important to note that if your idcolumn is an autoincrement column then you might as well omit it from your INSERT all together and let mysql increment it as normal.

同样重要的是要注意，如果您的id列是自动增量列，那么您最好将它从 INSERT 中全部省略，并让 mysql 像往常一样增加它。

回答by Russ Cam

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

不能将 WHERE 子句与 VALUES 子句结合使用。据我所知，您有两种选择-

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
   

1. INSERT 指定值

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

2. 使用 SELECT 语句插入

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

回答by Rob Prouse

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

您可以将 WHERE 子句用于 UPDATE 查询。当您插入时，您假设该行不存在。

The OP's statement would then become;

OP 的声明将变为；

UPDATE Users SET weight = 160, desiredWeight = 45 where id = 1;

UPDATE Users SET weight = 160, desiredWeight = 45 where id = 1;

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.

在 MySQL 中，如果要 INSERT 或 UPDATE，可以使用带有 WHERE 子句的 REPLACE 查询。如果 WHERE 不存在，则插入，否则更新。

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.

我认为Bill Karwin 的观点非常重要，可以从评论中提取出来并使其变得非常明显。谢谢比尔，我已经很久没有使用 MySQL 了，我记得我在使用 REPLACE 时遇到了问题，但我忘记了它们是什么。我应该查一下。

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.

这不是 MySQL 的 REPLACE 的工作方式。它执行 DELETE（如果该行不存在，则可能是空操作），然后执行 INSERT。想想后果可见。触发器和外键依赖。相反，使用 INSERT...ON DUPLICATE KEY UPDATE。

回答by Daniel A. White

I do not believe the insert has a WHERE clause.

我不相信插入有 WHERE 子句。

回答by Somnath Muluk

Insert query doesn't support where keyword*

插入查询不支持 where 关键字*

Conditions apply because you can use where condition for sub-select statements. You can perform complicated inserts using sub-selects.

条件适用是因为您可以将 where 条件用于子选择语句。您可以使用子选择执行复杂的插入。

For example:

例如：

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a "select" in the insert statement, you can perform multiples inserts quickly.

通过在插入语句中放置一个“选择”，您可以快速执行多次插入。

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement before performing the insert.

使用这种类型的插入，您可能希望检查插入的行数。您可以通过在执行插入之前运行以下 SQL 语句来确定将要插入的行数。

SELECT count(*)
FROM customers
WHERE city = 'Newark';

You can make sure that you do not insert duplicate information by using the EXISTS condition.

您可以使用 EXISTS 条件确保不插入重复信息。

For example, if you had a table named clients with a primary key of client_id, you could use the following statement:

例如，如果您有一个名为 clients 的表，其主键为 client_id，则可以使用以下语句：

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.

此语句使用子选择插入多条记录。

If you wanted to insert a single record, you could use the following statement:

如果要插入单个记录，可以使用以下语句：

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

双表的使用允许您在选择语句中输入您的值，即使这些值当前未存储在表中。

See also How to insert with where clause

另请参阅如何使用 where 子句插入

回答by sakhunzai

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:

b)。如果记录已存在，请使用更新而不是插入：

 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

C）。如果要同时更新或插入

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

d)。如果您想从 SAME 表中 CLONE 记录，请记住您无法从要插入的表中进行选择

 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

我认为这很好地涵盖了大多数情况

回答by Borniet

You simply cannot use WHERE when doing an INSERT statement:

在执行 INSERT 语句时，您根本不能使用 WHERE：

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

should be:

应该：

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

The WHERE part only works in SELECT statements:

WHERE 部分仅适用于 SELECT 语句：

SELECT from Users WHERE id = 1;

or in UPDATE statements:

或在 UPDATE 语句中：

UPDATE Users set (weight = 160, desiredWeight = 145) WHERE id = 1;

回答by Richard L

Insert into = Adding rows to a table

插入 = 向表中添加行

Upate = update specific rows.

Upate = 更新特定行。

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

where 子句将在您的插入中描述什么？它没有任何匹配项，该行不存在（还）...

回答by Igor Vujovic

You can do conditional INSERT based on user input. This query will do insert only if input vars '$userWeight' and '$userDesiredWeight' are not blank

您可以根据用户输入进行有条件的 INSERT。仅当输入变量 '$userWeight' 和 '$userDesiredWeight' 不为空时，此查询才会插入

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

回答by Marc Alff

Does WHERE-clause can be actually used with INSERT-INTO-VALUES in any case?

在任何情况下，WHERE 子句都可以与 INSERT-INTO-VALUES 一起使用吗？

The answer is definitively no.

答案肯定是否定的。

Adding a WHERE clause after INSERT INTO ... VALUES ... is just invalid SQL, and will not parse.

在 INSERT INTO ... VALUES ... 之后添加 WHERE 子句只是无效的 SQL，不会解析。

The error returned by MySQL is:

MySQL返回的错误是：

mysql> INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1

The most important part of the error message is

错误消息中最重要的部分是

... syntax to use near 'WHERE id = 1' ...

which shows the specific part the parser did not expect to find here: the WHERE clause.

它显示了解析器没想到在这里找到的特定部分：WHERE 子句。