Update without offer

Question

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

I want to update the second line number. to 2. I can use the request

update test set no = 2 where id = 11;

How can I achieve the same without having a place?

Answer 1

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

EDIT

As a continuation, to clarify the query results above ...

In the case where idit is not unique in the table, the query can potentially update more than one row. The inline view ( s) query retrieves the id value from the "second" row after the rows are ordered by id value. Then all rows with the same id value will be updated.

, id ; id , ( ) .

, :

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

id 11.

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

Answer 2

, , ...

UPDATE `test` SET `no` = IF(`id`=11, 1, `no`);

, , , .

Answer 3

, , . , :

update test set no = case id when 11 then 2 else no end

Answer 4

...

update test A INNER JOIN test B 
  on A.ID = B.ID 
 and B.ID = 11
 set A.No = 2;

, 11, 11 (b.iD = 11). ON.

Answer 5

This does not use a sentence where, and it may be slightly faster than using if()or case:

update test t join
       (select 1 as dum) dum
       on t.id = 11
    set t.no = 2 ;