Sql help for update query

[eluser]debow[/eluser]
Hello all, I'm posting there here because I'm using CI for my site. I know this is more a mysql question but thought I'd try.

What I'm trying to do is take the below select that works for the most part as a select and covert it to an update as well which I'm having problems with around the JOIN.

This is the select I'm working off of.

Code:

SELECT id, name, result, event
,      FIND_IN_SET(
            result
        ,  (SELECT  GROUP_CONCAT(
                        DISTINCT result
                        ORDER BY result
                    )
            FROM    eresults where event='100m' and result !='DNC')
        ) as rank
FROM   eresults where event='100m';

My table consist of another column called eventrank. I want to take the value of rank and set the eventrank to the same based off the id etc. I'm trying something like below but its not working.

Code:

UPDATE eresults
JOIN
SELECT id, name, result, event
,      FIND_IN_SET(
            result
        ,  (SELECT  GROUP_CONCAT(
                        DISTINCT result
                        ORDER BY result
                    )
            FROM eresults where event='100m' and result !='DNC')
        ) as rank
FROM eresults where event='100m'
SET eventrank = rank where eresults.id=rank.id;

Any help would be appreciated. Thanks

[eluser]debow[/eluser]
I may have got it with this. Still testing it but my first run seems to have worked.

Code:

UPDATE eresults
JOIN(
SELECT id, name, result, event
,      FIND_IN_SET(
            result
        ,  (SELECT  GROUP_CONCAT(
                        DISTINCT result
                        ORDER BY result
                    )
            FROM eresults where event='100m' and result !='DNC')
        ) as rank
FROM eresults where event='100m')
rank ON (rank.id = eresults.id)
SET eventrank = rank.rank;