mysql - Updating Column based on values from values in its own table -

basically trying execute query

update communication_relevance set score = (select ((ces.expert_score * cirm.consumer_rating) + (12.5 * scs.similarity)* (1 - exp(-0.5 * (cal.tips_amount / at.avg_tips)) + .15))as answer_score communication_relevance cr join network_communications nc on cr.communication_id=nc.communications_id join consumer_action_log cal on cr.action_log_id=cal.action_log_id join communication_interest_mapping cim on nc.parent_communications_id=cim.communication_id join consumer_interest_rating_mapping cirm on cr.consumer_id=cirm.consumer_id     , cim.consumer_interest_expert_id=cirm.consumer_interest_id join consumer_expert_score ces on nc.sender_consumer_id=ces.consumer_id     , cim.consumer_interest_expert_id=consumer_expert_id join survey_customer_similarity scs on cr.consumer_id=scs.consumer_id_2     , cal.sender_consumer_id=scs.consumer_id_1  or cr.consumer_id=scs.consumer_id_1     , cal.sender_consumer_id=scs.consumer_id_2 cross join (select avg(tips_amount) avg_tips consumer_action_log join communication_relevance on consumer_action_log.sender_consumer_id=communication_relevance.consumer_id) at) ; 

but error:

error:1/25/2011 1:03:20 pm 0:00:00.135: lookup error - mysql database error: can't specify target table 'communication_relevance' update in clause 

any appreciated!

you use update (.. join ..) set syntax

update communication_relevance x join (     select cr.communication_id, ((ces.expert_score * cirm.consumer_rating)         + (12.5 * scs.similarity)         * (1 - exp(-0.5 * (cal.tips_amount / at.avg_tips)) + .15)) answer_score     communication_relevance cr     join network_communications nc on cr.communication_id=nc.communications_id     join consumer_action_log cal on cr.action_log_id=cal.action_log_id     join communication_interest_mapping cim on nc.parent_communications_id=cim.communication_id     join consumer_interest_rating_mapping cirm on cr.consumer_id=cirm.consumer_id         , cim.consumer_interest_expert_id=cirm.consumer_interest_id      join consumer_expert_score ces on nc.sender_consumer_id=ces.consumer_id         , cim.consumer_interest_expert_id=consumer_expert_id     join survey_customer_similarity scs on         cr.consumer_id=scs.consumer_id_2 , cal.sender_consumer_id=scs.consumer_id_1       or cr.consumer_id=scs.consumer_id_1 , cal.sender_consumer_id=scs.consumer_id_2     cross join     (         select avg(l.tips_amount) avg_tips         consumer_action_log l         join communication_relevance r on l.sender_consumer_id=r.consumer_id     ) @ ) on x.communication_id = at.communication_id set x.score = at.answer_score; 

as proof of concept else reading this, here table can create , try syntax on

create table user_news(     user_id int, article_id int, article_date timestamp,     primary key(user_id, article_id)); insert user_news select 1,2,'2010-01-02'; insert user_news select 1,3,'2010-01-03'; insert user_news select 1,4,'2010-01-01'; insert user_news select 2,1,'2010-01-01'; insert user_news select 2,2,'2010-01-02'; insert user_news select 2,3,'2010-01-02'; insert user_news select 2,4,'2010-01-02'; insert user_news select 4,5,'2010-01-05'; 

now run update (it sets article_date of records max article_date same user)

update user_news join (   select b.user_id, max(b.article_date) adate   user_news b   group b.user_id) c   on a.user_id=c.user_id set a.article_date = c.adate; 

finally, inspect contents

select * user_news;