mysql:where子句对delete语句无效，但对select语句却可以正常工作？

w1e3prcc 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(250)

我试图在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);

有什么想法吗？
编辑：根据第一个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

sql mysql

来源：https://stackoverflow.com/questions/51922940/mysql-how-can-where-clause-be-invalid-for-delete-statement-but-work-fine-for-se

2条答案

按热度按时间

tcbh2hod1#

错误不是抱怨 WHERE 子句中，它正在抱怨使用了以前未声明的表别名 FROM . 您需要将查询重写为

DELETE tr FROM timeclock_raw tr WHERE ...

查看手册的最后一页。

赞(0）回复(0）举报 2021-06-20

5gfr0r5j2#

mysql不允许您在查询的其余部分使用被删除（或更新）的表。通常的解决办法是 join 学生：

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;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql:where子句对delete语句无效，但对select语句却可以正常工作？

2条答案

相关问题

热门标签

最新问答