代码之家 › 专栏 › 技术社区 › Álvaro González

选择并删除

mysql-error-1093 innodb sql mysql

2

Álvaro González · 技术社区 · 15 年前

我需要从表中删除某些行。必须删除哪些行是我通过查询发现的。然而, it appears that you cannot do both operations (select and delete) in the same query :

当前,不能从表并从同一表中选择在子查询中。

所以我不能这样做:

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

如何在纯MySQL中实现这个记录清理?

服务器运行mysql 5.1.39。

3 回复 | 直到 14 年前

1

joshperry 15 年前

尝试使用联接执行DELETE语句

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)

2

1

Michael Pakhantsov 15 年前

 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)

3

0

dan04 15 年前

使用 UPDATE 将行标记为删除,然后 DELETE 删除它们。

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