How do I update the table(MYSQL)

Question

the system throw a error "You can't specify target table 'screening' for update in FROM clause" How do I completed the update like this?

 UPDATE screening
  SET maileddate = date('Y-m-d', strtotime($mailed_date[$screeningId]))
  WHERE user_id IN (SELECT id FROM users
  INNER JOIN screening ON
  (users.id = screening.users_id
  AND screening.id = {$screeningId}))
  AND date BETWEEN 05-15/2011 AND 11-15-2011
  LIMIT 2

Answer 1

You would have to remove the LIMIT option (similar to a TOP query in other SQL languages). You are basically saying find me the first 2 items and UPDATE. This may produce unexpected results on tables due to sort order.