CODEWITHSUNDEEP
mysql
userxöa
userxöa

Reputation: 63

Update table set same value to all rows with duplicate value

I have a table of sport results. When using following code, the table get places according to the times.

SET @pos := 0;
UPDATE table SET Place = ( SELECT @pos := @pos + 1 ) ORDER BY Time ASC;

In case of same times (like rows 1,3 and 4,5), it updates it according to the ID-s, so the result is following:

ID | Time     | Place
1  | 00:12:14 | 1
2  | 00:12:18 | 3
3  | 00:12:14 | 2
4  | 00:12:25 | 4
5  | 00:12:25 | 5

How could I update the table so, that if there is multiple rows of same time, all rows would get the best place (like in following table)?

ID | Time     | Place
1  | 00:12:14 | 1
2  | 00:12:18 | 3
3  | 00:12:14 | 1
4  | 00:12:25 | 4
5  | 00:12:25 | 4

Upvotes: 0

Views: 437

Answers (1)

forpas
forpas

Reputation: 164099

You can do it with a join of the table to a query returning for each row the number of rows less than its Time:

update tablename t inner join (
  select t.id, (
    select count(*) counter from tablename
    where time < t.time
  ) counter
  from tablename t
) c on c.id = t.id
set t.place = c.counter + 1;

See the demo.
Results:

| ID  | Time     | Place |
| --- | -------- | ----- |
| 1   | 00:12:14 | 1     |
| 2   | 00:12:18 | 3     |
| 3   | 00:12:14 | 1     |
| 4   | 00:12:25 | 4     |
| 5   | 00:12:25 | 4     |

Upvotes: 1

Related Questions