SELECT and DELETE

Question

I need to remove certain rows from a table. Which rows must be deleted is something I find out through a query. However, it appears that you cannot do both operations (select and delete) in the same query:

Currently, you cannot delete from a table and select from the same table in a subquery.

So I cannot do this:

DELETE
FROM telefono
WHERE telefono_id IN (
    SELECT te.telefono_id
    FROM telefono te
    LEFT JOIN centro_telefono ce ON te.telefono_id=ce.telefono_id AND ce.telefono_id IS NOT NULL
    LEFT JOIN contacto_telefono co ON te.telefono_id=co.telefono_id AND co.telefono_id IS NOT NULL
    WHERE COALESCE(ce.telefono_id, co.telefono_id) IS NULL AND te.fecha_alta < DATE_SUB(NOW(), INTERVAL 1 DAY)
);
-- SQL Error (1093): You can't specify target table for update in FROM clause

How can I implement this record clean-up in pure MySQL?

The server runs MySQL 5.1.39.

Answer 1

 CREATE TEMPORARY TABLE tmptable
 SELECT te.telefono_id
 FROM telefono te
  LEFT JOIN centro_telefono ce ON te.telefono_id=ce.telefono_id AND ce.telefono_id IS NOT NULL
  LEFT JOIN contacto_telefono co ON te.telefono_id=co.telefono_id AND co.telefono_id IS NOT NULL
  WHERE COALESCE(ce.telefono_id, co.telefono_id) IS NULL AND te.fecha_alta < DATE_SUB(NOW(), INTERVAL 1 DAY)

 DELETE FROM telefono te
 WHERE te.telefono_id IN (Select telefono_id from tmptable)

Answer 2

Use UPDATE to mark rows for deletion, then DELETE to actually delete them.

UPDATE telefono SET marker_column='DELETE ME!!!!' WHERE telefono_id IN (...);
DELETE FROM telefono WHERE marker_column='DELETE ME!!!!';

Answer 3

Try doing the delete statement with the joins

DELETE te
FROM telefono as te
    LEFT JOIN centro_telefono ce
        ON te.telefono_id=ce.telefono_id AND ce.telefono_id IS NOT NULL
    LEFT JOIN contacto_telefono co
        ON te.telefono_id=co.telefono_id AND co.telefono_id IS NOT NULL
WHERE
    COALESCE(ce.telefono_id, co.telefono_id) IS NULL
    AND te.fecha_alta < DATE_SUB(NOW(), INTERVAL 1 DAY)