我正试图在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
发布于 2018-08-20 10:29:00
MySQL不允许您在查询的其余部分中使用正在删除(或更新)的表。正常的解决方案是使用join
s:
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;
发布于 2018-08-20 09:07:20
错误不是在抱怨WHERE
子句,而是在抱怨之前没有在FROM
之前声明的表别名的使用。您需要将查询重写为
DELETE tr FROM timeclock_raw tr WHERE ...
查看manual页面的最末尾。
https://stackoverflow.com/questions/51922940
复制相似问题