+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Sql help for update query (/showthread.php?tid=35736)
Sql help for update query - El Forum - 11-09-2010
[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
Sql help for update query - El Forum - 11-09-2010
[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;