Reputation: 23
I have a problem with this delete query:
DELETE r
FROM table AS r
WHERE r.stoptime IS NULL
and r.address IN
(select address from table where starttime <= r.starttime and stoptime > r.starttime)
I get the following error:
Error : You can't specify target table 'r' for update in FROM clause.
My goal is to delete records that the starttime is contained in another record but I got an error with the alias in the subquery.
Somebody know how to do this? Thanks in advance.
Upvotes: 1
Views: 167
Reputation: 172628
Try to use JOINS like this:
DELETE r
FROM `table` r
JOIN `table` t ON t.id = r.id
WHERE t.starttime <= r.starttime and t.stoptime > r.starttime
AND r.stoptime IS NULL
Upvotes: 1