CODEWITHSUNDEEP
mysqljoinsql-updatemax
Alex Ception
Alex Ception

Reputation: 314

Update row with join and max

I have the two following tables (simplified schema) :

score
-----
id
score

score_history
-------------
id
score_id (foreign key with score table)
score

I'm populating the score_history table periodically. I would like to update for every row in the score table, the score column based on the score in score_history with the largest id number associated.

An example could be :

score entries
+----+-------+
| id | score |
+----+-------+
| 1  | 0     |
| 2  | 0     |
+----+-------+

score_history entries
+----+----------+-------+
| id | score_id | score |
+----+----------+-------+
| 1  | 1        | 15    |
| 2  | 2        | 10    |
| 3  | 1        | 14    |
| 4  | 2        | 11    |
+----+----------+-------+

Before entries 3/4 exists in score_history I would like, in one request to update score from score table to be the following:

+----+-------+
| id | score |
+----+-------+
| 1  | 15    |
| 2  | 10    |
+----+-------+

After inserting entries 3/4 in score_history, again I would like with the same request having my score table like:

+----+-------+
| id | score |
+----+-------+
| 1  | 14    |
| 2  | 11    |
+----+-------+

I tried multiple things (like https://stackoverflow.com/a/9396897/916630) but could not manage to success.

Any ideas ?

Upvotes: 1

Views: 3376

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

If you are looking for an update command it could be as

update 
score s 
join score_history sh on sh.score_id = s.id 
join ( 
  select max(id) as max_id, score_id  from score_history group by score_id
)x on x.max_id = sh.id and x.score_id = sh.score_id 
set s.score = sh.score ;

Upvotes: 2

amow
amow

Reputation: 2223

update score 
join score_history on score.id = score_history.score_id 
join 
(select score_id, max(id) mid
from score_history
group by score_id) t
on score_history.id = t.mid
set score.score = score_history.score

First get the max(history_id) for each score_id in history table.

Then join the history with the max id.

At last join the score table and set the score column to the score having max id

Upvotes: 1

DSF
DSF

Reputation: 804

UPDATE score s
SET score = 
   (SELECT score FROM score_history sh WHERE sh.score_id = s.ID  ORDER BY SH.id ASC LIMIT 1)

Upvotes: 1

Related Questions