MySQL:where 子句如何对 delete 语句无效但对 select 语句工作正常？

Question

我正在尝试在 mysql 中运行删除语句 - 我收到错误消息。为了测试，我尝试将它作为“select *”而不是“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);

有什么想法吗？

编辑:根据第一 react ，我尝试了这个但又遇到了另一个错误......有什么想法吗？

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

Answer 1

错误不是提示 WHERE 子句，而是提示使用了以前未在 FROM 之前声明的表别名。您需要将查询重写为

DELETE tr FROM timeclock_raw tr WHERE ...

查看 manual 的最后页。