ERROR 1093 (HY000): You can't specify target table

[hbmarar]

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..

Regards

Harish

[dickersonka]

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

[hbmarar]

Hi dickersonka,

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

[dickersonka]

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 = 
}