MySQL: How can where clause be invalid for delete statement but work fine for select statement?

I am trying to run a delete statement in mysql - I get an error. To test I tried running it as "select *" instead of "delete". Nothing else changed, and it runs fine. Any idea why?

here is the full delete statement

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);


I get this error

[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


but this runs fine...

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);


Any ideas?

EDIT: based on first response I tried this but got another error... any ideas?

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


from

delete

DELETE table_name WHERE...

from

FROM

2 Answers
2

MySQL does not allow you to use the table being deleted (or updated) in the rest of the query. The normal solution is to use joins:

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;


The error is not complaining about the WHERE clause, it is complaining about the use of a table alias that was not previously declared before the FROM. You need to rewrite the query as

WHERE

FROM

DELETE tr FROM timeclock_raw tr WHERE ...


Check out the very end of the manual page.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.