Select Inner join and update in single query

Question

I want to find ranking and then update their rank column according to their id.

My data is as follows (Table Member).

MEMBER_ID | LOAN_AMOUNT | Rank 
1         | 2,000.00    |  0
2         | 1,000.00    |  0
3         | 4,000.00    |  0
4         | 1,000.00    |  0

Now I want to find their ranking and update rank column according to it. My general query is something like it:

UPDATE
Member AS dest,
(
    SELECT RANK() OVER(ORDER BY t.loan_amount DESC) as [rank],
     t.memeber_id,t.loan_amount
FROM Member
) AS src
SET
dest.rank = src.rank
INNER JOIN dest.memeber_id = src.memeber_id

Is there any faster way to update my table?

Answer 1

In mysql the update join sintax should be

UPDATE
Member AS dest
INNER JOIN 
(
    SELECT RANK() OVER(ORDER BY t.loan_amount DESC) as [rank],
     t.memeber_id,t.loan_amount
FROM Member
) src ON dest.memeber_id = src.memeber_id
SET dest.rank = src.rank

anyway you should avoid store data easly calcuated on fly .. in the same time you perform the select can easly obtain also the rank()