Ad:

MySQL Discussion Thread
Unsolved
Views: 5142

Nov 10th, 2008

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

Expand Post »

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

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.

hbmarar

Reputation Points: 10

Solved Threads: 0

Junior Poster

Offline

187 posts
since Apr 2005

Permalink

Nov 10th, 2008

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

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

Permalink

Nov 10th, 2008

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

Hi dickersonka,

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

hbmarar

Reputation Points: 10

Solved Threads: 0

Junior Poster

Offline

187 posts
since Apr 2005

Permalink

Nov 10th, 2008

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

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

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

dickersonka

Reputation Points: 133

Solved Threads: 141

Veteran Poster

Offline

1,162 posts
since Aug 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: please help me using ssh (very urgent)

Next Thread in MySQL Forum Timeline: How to search over 3 tables

DaniWeb Message

Cancel Changes

User Name
Password