CODEWITHSUNDEEP
mysqlsql-update
stil
stil

Reputation: 5566

Update column to 1 if value is highest in group, 0 otherwise

I have a table with following columns:

player_id, datetime, player_level, is_latest

What I need to do, is to set is_latest = 1, when datetime is highest for the player.

This is how I would check highest datetime for players:

SELECT player_id, MAX(datetime)
FROM players
GROUP BY player_id

And now I need to update whole table:

UPDATE players
SET is_latest = /* 1 if datetime = MAX(datetime), 0 otherwise */

Do you have any ideas how to write UPDATE statement? Best if I could have SELECT and UPDATE in single SQL query.

The table has 30 millions of rows, so I would like a solution that doesn't involve copying all rows somewhere else.

Upvotes: 0

Views: 80

Answers (1)

jpw
jpw

Reputation: 44901

I don't know if it's the fastest way, but this query should do what you want and give you an idea about the general syntax:

UPDATE players p
LEFT JOIN (
  SELECT player_id, MAX(datetime) max_date
  FROM players
  GROUP BY player_id
) t ON p.player_id = t.player_id AND p.datetime = t.max_date
SET p.is_latest = (t.max_date IS NOT NULL);

Using a left join means that rows that don't match the p.datetime = t.max_date predicate will be null, a fact that we can use in a boolean expression in the set clause to ensure that only the latest rows will be set to one and the older will be set to 0.

See this SQL Fiddle for an example.

As always, when it comes to testing updates and such, make sure to have a copy/backup...

Upvotes: 1

Related Questions