CODEWITHSUNDEEP
mysql
KimBenGon
KimBenGon

Reputation: 315

MySQL, I want to change the value of another table with the largest value

There are two tables

A
ENO | VALUE |  YMD
 1  |   3   | 190308   
 1  |   10  | 190309
 1  |   5   | 190310 

B
ENO | TARGET | 
 1  |   10   |

We want to update the TARGET column of table B to the most recent date with the value "VALUE".

I want to change the result of the B table as follows.

B
ENO | TARGET |
 1  |    5   |

What should I do?

Upvotes: 0

Views: 28

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below -

update tableB A 
join 
(select * from tableA x where ymd in (select max(ymd) from tableA x1 where x.eno=x1.eno)
)B on A.eno=B.eno
set A.target=B.target

Upvotes: 1

Related Questions