4

I have got MySQL error "You can't specify target table 'tasks' for update in FROM clause" running the following query:

DELETE FROM tasks
WHERE tasks.id IN 
(
SELECT tasks.id
FROM tasks 
    JOIN deadlines ON deadlines.id = deadline_id
WHERE DATE_ADD(tasks.created_at, INTERVAL deadlines.duration DAY) <= NOW()
)

How can I manage this?

Thanx!

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
ohavryl
  • 397
  • 1
  • 5
  • 17

2 Answers2

10

You can wrap it in a subquery like so. The issue is that MySQL can't update rows that it's also querying. This will make MySQL use a temporary table implicitly to store the ids you want to delete.

DELETE FROM tasks
WHERE tasks.id IN 
(
SELECT id FROM
(
SELECT tasks.id
FROM tasks 
    JOIN deadlines ON deadlines.id = deadline_id
WHERE DATE_ADD(tasks.created_at, INTERVAL deadlines.duration DAY) <= NOW()
) AS taskstodelete
)
Novikov
  • 4,399
  • 3
  • 28
  • 36
1

It's because you're specifying the tasks table more than once. Try:

DELETE FROM tasks
USING deadlines
WHERE deadlines.id = tasks.deadline_id
AND DATE_ADD(tasks.created_at, INTERVAL deadlines.duration DAY) <= NOW()
Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154