不带where子句的更新 [英] Update without where clause

问题描述

+------+------+
| id   | no   |
+------+------+
|    1 |    1 |
|   11 |    1 |
|   21 |    1 |
+------+------+

我想更新第二行.至2. 我可以使用的查询是

I want to update 2nd row no. to 2. the query i can use is

update test set no = 2 where id = 11;

如果没有where子句，我该如何实现相同的目标?

How can i achieve the same without where clause ??

推荐答案

要更新表中的第二"行，该行具有第二个最小id值...

To update the "second" row in the table, the row that has the second smallest id value...

UPDATE test t
  JOIN ( SELECT r.id
           FROM test r
          ORDER BY r.id 
          LIMIT 1,1
       ) s
    ON s.id = t.id
   SET t.no = 2

编辑

作为澄清上述查询结果的后续措施...

As a followup to clarify the results of the query above...

在表中id不是唯一的情况下，查询可能会更新多于一个行.内联视图查询(s)在按ID值对行进行排序之后，从第二"行获取ID值.然后将更新具有相同ID值的所有行.

In the case where id is not unique in the table, the query could potentially update more than one row. The inline view query (s) gets the id value from the "second" row, after the rows are ordered by id value. Then all rows that have that same id value would be updated.

仅当>不是唯一时，这才是问题.如果id是唯一的，则该语句将(最多)更新一行.

This is an issue only if id is not unique; if id is unique, the statement would update (at most) one row.

例如，如果表的内容为:

For example, if the contents of the table was:

+-----+-----+
| id  | no  |
+-----+-----+
|   1 |   1 |
|  11 |   3 | <-- "second" row, ordered by id ascending 
|  11 |   4 | <-- id from third row matches id from second row
|  21 |   1 |
+-----+-----+

上面查询的结果将是更新具有11值的两行.

The result of the query above would be to update the two rows that have id value of 11.

+-----+-----+
| id  | no  |
+-----+-----+
|   1 |   1 |
|  11 |   2 |  <-- updated
|  11 |   2 |  <-- updated
|  21 |   1 |
+-----+-----+

这篇关于不带where子句的更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！