MySQL: where子句如何对delete语句无效，但对select语句有效？
MySQL: where子句如何对delete语句无效，但对select语句有效？
提问于 2018-08-20 08:48:42
我正试图在mysql中运行一条delete语句--我得到一个错误。为了测试，我试着用"select *“而不是"delete”来运行它。没有其他的改变，它运行得很好。知道为什么吗？
下面是完整的delete语句
DELETE
from timeclock_raw tr
where tr.rectype = 'active'
and tr.recdate > '2018-08-17'
and tr.seconds < (select max(tr1.seconds) as secs
from timeclock_raw tr1
where tr1.env = tr.env and tr1.intid = tr.intid and tr1.studycode = tr.studycode and tr1.recdate = tr.recdate)
and tr.seconds > (select min(tr2.seconds) as secs
from timeclock_raw tr2
where tr2.env = tr.env and tr2.intid = tr.intid and tr2.studycode = tr.studycode and tr2.recdate = tr.recdate);我得到了这个错误
[2018-08-19 20:46:51] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tr
[2018-08-19 20:46:51] where tr.rectype = 'active'
[2018-08-19 20:46:51] and tr.recdate > '2018-08-17'
[2018-08-19 20:46:51] and tr.' at line 2但是这个运行的很好..。
select *
from timeclock_raw tr
where tr.rectype = 'active'
and tr.recdate > '2018-08-17'
and tr.seconds < (select max(tr1.seconds) as secs
from timeclock_raw tr1
where tr1.env = tr.env and tr1.intid = tr.intid and tr1.studycode = tr.studycode and tr1.recdate = tr.recdate)
and tr.seconds > (select min(tr2.seconds) as secs
from timeclock_raw tr2
where tr2.env = tr.env and tr2.intid = tr.intid and tr2.studycode = tr.studycode and tr2.recdate = tr.recdate);有什么想法吗？
编辑:基于第一个响应，我尝试了这个，但得到了另一个错误...有什么想法吗？
sql> delete tr
from timeclock_raw tr
where tr.rectype = 'active'
and datediff(now(),tr.recdate) < 3
and tr.seconds < (select max(tr1.seconds) as secs
from timeclock_raw tr1
where tr1.env = tr.env and tr1.intid = tr.intid and tr1.studycode = tr.studycode and tr1.recdate = tr.recdate)
and tr.seconds > (select min(tr2.seconds) as secs
from timeclock_raw tr2
where tr2.env = tr.env and tr2.intid = tr.intid and tr2.studycode = tr.studycode and tr2.recdate = tr.recdate)
[2018-08-20 01:07:24] [HY000][1093] You can't specify target table 'tr' for update in FROM clause
[2018-08-20 01:07:24] [HY000][1093] You can't specify target table 'tr' for update in FROM clause回答 2
Stack Overflow用户
回答已采纳
发布于 2018-08-20 10:29:00
MySQL不允许您在查询的其余部分中使用正在删除(或更新)的表。正常的解决方案是使用joins：
delete tr
from timeclock_raw tr join
(select tr1.env, tr1.intid, tr1.studycode, tr1.recdate, min(tr1.seconds) as minsecs, max(tr1.seconds) as maxsecs
from timeclock_raw tr1
group by tr1.env, tr1.intid, tr1.studycode, tr1.recdate
) x
on tr1.env = tr.env and
tr1.intid = tr.intid and
tr1.studycode = tr.studycode and
tr1.recdate = tr.recdate
where tr.rectype = 'active' and
tr.recdate > '2018-08-17' and
tr.seconds < x.maxsecs and
tr.seconds > x.minsecs;Stack Overflow用户
发布于 2018-08-20 09:07:20
错误不是在抱怨WHERE子句，而是在抱怨之前没有在FROM之前声明的表别名的使用。您需要将查询重写为
DELETE tr FROM timeclock_raw tr WHERE ...查看manual页面的最末尾。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51922940
复制相关文章
相似问题