CODEWITHSUNDEEP
mysqlsubqueryalias
Android Learner
Android Learner

Reputation: 23

Delete subquery with alias MySQL

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

Answers (1)

Rahul Tripathi
Rahul Tripathi

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

Related Questions