如何像这样处理MYSQL查询更新 [英] how to handle MYSQL query update like this

问题描述

系统抛出错误你不能在FROM子句中为更新指定目标表'筛选'"我如何完成这样的更新?

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  

推荐答案

你得到那个错误，因为你试图更新 screening 表，同时从同一个表中获取 id(与 users 一起加入).解决方法是使用子查询，例如:

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.

这篇关于如何像这样处理MYSQL查询更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！