Member Avatar for 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

  • 2 Contributors
  • 3 Replies
  • 360 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by dickersonka
Member Avatar for dickersonka

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

Member Avatar for hbmarar

Hi dickersonka,

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

Member Avatar for 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 = 
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.