title: Updating Column based on values from values in its own table
body: <p>Basically I am trying to execute this query</p>
  
  <pre><code>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
  FROM COMMUNICATION_RELEVANCE AS cr
  JOIN network_communications AS nc
  ON cr.COMMUNICATION_ID=nc.COMMUNICATIONS_ID
  JOIN consumer_action_log AS cal
  ON cr.ACTION_LOG_ID=cal.ACTION_LOG_ID
  JOIN communication_interest_mapping AS cim
  ON nc.PARENT_COMMUNICATIONS_ID=cim.COMMUNICATION_ID
  JOIN consumer_interest_rating_mapping AS cirm
  ON cr.CONSUMER_ID=cirm.CONSUMER_ID
      AND cim.CONSUMER_INTEREST_EXPERT_ID=cirm.CONSUMER_INTEREST_ID
  JOIN consumer_expert_score AS ces
  ON nc.SENDER_CONSUMER_ID=ces.CONSUMER_ID
      AND cim.CONSUMER_INTEREST_EXPERT_ID=CONSUMER_EXPERT_ID
  JOIN survey_customer_similarity AS scs
  ON cr.CONSUMER_ID=scs.CONSUMER_ID_2
      AND cal.SENDER_CONSUMER_ID=scs.CONSUMER_ID_1 
  OR cr.CONSUMER_ID=scs.CONSUMER_ID_1
      AND cal.SENDER_CONSUMER_ID=scs.CONSUMER_ID_2
  CROSS JOIN
  (SELECT AVG(TIPS_AMOUNT) AS AVG_TIPS
  FROM CONSUMER_ACTION_LOG
  JOIN COMMUNICATION_RELEVANCE
  ON CONSUMER_ACTION_LOG.SENDER_CONSUMER_ID=COMMUNICATION_RElEVANCE.consumer_id) AT)
  ;
  </code></pre>
  
  <p>But I get this error:</p>
  
  <pre><code>Error:1/25/2011 1:03:20 PM 0:00:00.135: Lookup Error - MySQL Database Error: You can't specify target table 'communication_relevance' for update in FROM clause
  </code></pre>
  
  <p>Any help would be much appreciated!</p>