[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
