MySQL

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

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2005

Posts: 183

Reputation: hbmarar is an unknown quantity at this point

Solved Threads: 0

hbmarar

Offline

Junior Poster

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

Nov 10th, 2008

Hi to xperts here,

I do not know how to update with the follwing requirement

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

$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

Last edited by peter_budo; Nov 10th, 2008 at 8:00 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 138

dickersonka dickersonka is offline

Offline

Veteran Poster

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

Nov 10th, 2008

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

Custom Application & Software Development
www.houseshark.net

•

Join Date: Apr 2005

Posts: 183

Reputation: hbmarar is an unknown quantity at this point

Solved Threads: 0

hbmarar

Offline

Junior Poster

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

Nov 10th, 2008

Hi dickersonka,

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

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 138

dickersonka dickersonka is offline

Offline

Veteran Poster

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

Nov 10th, 2008

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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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 = 
}
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 = 
}