SQL ELIMINAR con UNIRSE a otra tabla para la condición WHERE
Tengo que eliminar filas de guide_category
que no tienen relación con la tabla guide
(relaciones muertas).
Esto es lo que quiero hacer, pero por supuesto no funciona.
DELETE FROM guide_category AS pgc
WHERE pgc.id_guide_category IN (SELECT id_guide_category
FROM guide_category AS gc
LEFT JOIN guide AS g ON g.id_guide = gc.id_guide
WHERE g.title IS NULL)
Error:
No se puede especificar la tabla de destino 'guide_category' para la actualización en la cláusula FROM
70
4 answers
Debido a los problemas de implementación de bloqueo, MySQL
no permite hacer referencia a la tabla afectada con DELETE
o UPDATE
.
Necesitas hacer un JOIN
aquí en su lugar:
DELETE gc.*
FROM guide_category AS gc
LEFT JOIN
guide AS g
ON g.id_guide = gc.id_guide
WHERE g.title IS NULL
O simplemente use un NOT IN
:
DELETE
FROM guide_category AS gc
WHERE id_guide NOT IN
(
SELECT id_guide
FROM guide
)
104
Author: Quassnoi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-30 15:31:09
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-30 15:31:09
Creo que, por su descripción, lo siguiente sería suficiente:
DELETE FROM guide_category
WHERE id_guide NOT IN (SELECT id_guide FROM guide)
Asumo, que no hay restricciones de integridad referencial en las tablas involucradas, ¿existen?
9
Author: Dirk,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-30 15:35:19
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-30 15:35:19
Pruebe este ejemplo de scripts SQL para facilitar la comprensión,
CREATE TABLE TABLE1 (REFNO VARCHAR(10))
CREATE TABLE TABLE2 (REFNO VARCHAR(10))
--TRUNCATE TABLE TABLE1
--TRUNCATE TABLE TABLE2
INSERT INTO TABLE1 SELECT 'TEST_NAME'
INSERT INTO TABLE1 SELECT 'KUMAR'
INSERT INTO TABLE1 SELECT 'SIVA'
INSERT INTO TABLE1 SELECT 'SUSHANT'
INSERT INTO TABLE2 SELECT 'KUMAR'
INSERT INTO TABLE2 SELECT 'SIVA'
INSERT INTO TABLE2 SELECT 'SUSHANT'
SELECT * FROM TABLE1
SELECT * FROM TABLE2
DELETE T1 FROM TABLE1 T1 JOIN TABLE2 T2 ON T1.REFNO = T2.REFNO
Su caso es:
DELETE pgc
FROM guide_category pgc
LEFT JOIN guide g
ON g.id_guide = gc.id_guide
WHERE g.id_guide IS NULL
4
Author: user2384628,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-05-15 07:11:47
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-05-15 07:11:47
Qué tal:
DELETE guide_category
WHERE id_guide_category IN (
SELECT id_guide_category
FROM guide_category AS gc
LEFT JOIN guide AS g
ON g.id_guide = gc.id_guide
WHERE g.title IS NULL
)
-2
Author: Philippe Leybaert,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-30 15:30:47
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-12-30 15:30:47