Hi to xperts here,

I do not know how to update with the follwing requirement

$score_update_query = "UPDATE participation_score SET score_last_update = '$date' , score = (IFNULL((SELECT avg(score)*0.8 FROM entity_score WHERE entity_score.contributor_id = contributor.contributor_id) , 4) + IFNULL((select participation_score*2 from participation_score where participation_score.contributor_id = contributor.contributor_id), 1) ) WHERE participation_score.user_id is null ; ";

as it shows error as

ERROR 1093 (HY000): You can't specify target table 'participation_score' for update in FROM clause.

I am stuck here and some help here would be great.. Not getting exactly what needs to be redone..



you can't use a subquery on the table you are wanting to be updated

Hi dickersonka,

Thanks for the time and reply. But still, wouldnt there be any alternative approach i could take..

yes, find which results you need to update

then loop through the results and create the updates there instead of combining it

here is some pseudo code, something like

select * from participation_score where user_id is null

foreach result {
 $score = //do your calculation that you had, selecting on a participation_score_id

//now update
update participation_score set //......
where participation_score_id =