Update without where clause

Question

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

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

update test set no = 2 where id = 11;

How can i achieve the same without where clause ??

Answer 1

I am not sure why you would want to but...

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

For the record, I would be surprised if this didn't perform horribly as it would go through every row in the table.

Answer 2

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 followup to clarify the results of the query above...

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.

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

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

Answer 3

That´s not possible, a update without where is an update to all the table. You can try this, but it is always like a where:

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