CODEWITHSUNDEEP
mysqlsql
Kishan Rajdev
Kishan Rajdev

Reputation: 1965

Update without where clause

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

Upvotes: 6

Views: 18369

Answers (5)

spencer7593
spencer7593

Reputation: 108510

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

Upvotes: 3

xQbert
xQbert

Reputation: 35353

And yet a 3rd way...

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

For clarity this does a self join on a table that only has record 11, thus updating only record 11 (b.iD = 11). using an ON Clause.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271171

This doesn't use a where clause and it might be a bit faster than using if() or case:

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

Upvotes: 1

bugs2919
bugs2919

Reputation: 358

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

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

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.

Upvotes: 5

Related Questions