how to handle MYSQL query update like this

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 get that error, because you are trying to update the screening table and at the same time getting the ids from that same table (joined with users). The workaround to this, is to use a subquery, as such:

UPDATE screening
   SET maileddate = date('Y-m-d', strtotime($mailed_date[$screeningId]))
 WHERE user_id IN (  
                    select s.id
                      from (

                        SELECT users.id
                          FROM users
                         INNER JOIN screening ON 
                                        users.id = screening.users_id
                                AND screening.id = {$screeningId}
                    ) as s)
   AND date BETWEEN 05-15/2011 AND 11-15-2011
LIMIT 2   

I only changed the indentation and added the subquery in lowercase.