seguro - safety update mysql

Error de MySQL 1093: no se puede especificar la tabla de destino para la actualización en la cláusula FROM

Question

Tengo una tabla story_category en mi base de datos con entradas corruptas. La siguiente consulta devuelve las entradas corruptas:

SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);

Intenté eliminarlos ejecutando:

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category
INNER JOIN story_category ON category_id=category.id);

Pero me sale el siguiente error:

# 1093 - No puede especificar la tabla de destino ''story_category'' para la actualización en la cláusula FROM

¿Cómo puedo superar esto?

Answer 1

¿Qué tal esta consulta espero que ayude

DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL

Answer 2

De acuerdo con http://dev.mysql.com/doc/refman/5.6/en/update.html vinculada a @CheekySoft, dice justo en la parte inferior.

Actualmente, no puede actualizar una tabla y seleccionar de la misma tabla en una subconsulta.

Supongo que está eliminando de store_category mientras sigue seleccionando en la unión.

Answer 3

Esto es lo que hice para actualizar un valor de columna de Prioridad en 1 si es> = 1 en una tabla y en su cláusula WHERE usando una subconsulta en la misma tabla para asegurarse de que al menos una fila contenga Prioridad = 1 (porque esa era la Condición a verificar mientras se realiza la actualización):

UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);

Sé que es un poco feo, pero funciona bien.

Answer 4

Intente guardar el resultado de la instrucción Select en una variable separada y luego úselo para eliminar la consulta.

Answer 5

La inner join en su sub-consulta es innecesaria. Parece que quieres eliminar las entradas en story_category donde category_id no está en la tabla de category .

Hacer esto:

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category);

En lugar de eso:

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);

Answer 6

La forma más sencilla de hacer esto es usar un alias de tabla cuando está refiriendo la tabla de consulta principal dentro de la consulta secundaria.

Ejemplo:

insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

Cambiarlo a:

insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));

Answer 7

Puede insertar los ID de las filas deseadas en una tabla temporal y luego eliminar todas las filas que se encuentran en esa tabla.

que puede ser lo que quiso decir @Cheekysoft al hacerlo en dos pasos.

Answer 8

Recientemente tuve que actualizar los registros en la misma tabla que hice a continuación:

UPDATE skills AS s, (SELECT id FROM skills WHERE type = ''Programming'') AS p
SET s.type = ''Development''
WHERE s.id = p.id;

Answer 9

Si algo no funciona, al pasar por la puerta delantera, tome la puerta trasera:

drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);
insert into apples values(''fuji'', 5), (''gala'', 6);
drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;
update apples_new
set price = (select price from apples where variety = ''gala'')
where variety = ''fuji'';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;

Es rápido. Cuanto más grandes sean los datos, mejor.

Answer 10

Si no puedes hacer

UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);

Porque es la misma tabla, puedes engañar y hacer:

UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[actualizar o eliminar o lo que sea]

Answer 11

prueba esto

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;

Answer 12

Actualización: Esta respuesta cubre la clasificación general de errores. Para obtener una respuesta más específica sobre cómo manejar mejor la consulta exacta del OP, consulte otras respuestas a esta pregunta

En MySQL, no puede modificar la misma tabla que usa en la parte SELECT.
Este comportamiento se documenta en: http://dev.mysql.com/doc/refman/5.6/en/update.html

Tal vez usted puede simplemente unirse a la mesa a sí mismo

Si la lógica es lo suficientemente simple como para redefinir la consulta, pierda la subconsulta y una la tabla a sí misma, empleando los criterios de selección apropiados. Esto hará que MySQL vea la tabla como dos cosas diferentes, permitiendo que los cambios destructivos sigan adelante.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Alternativamente, intente anidar la subconsulta más profundamente en una cláusula from ...

Si necesitas absolutamente la subconsulta, hay una solución alternativa, pero es fea por varias razones, incluido el rendimiento:

UPDATE tbl SET col = (
SELECT ... FROM (SELECT.... FROM) AS x);

La subconsulta anidada en la cláusula FROM crea una tabla temporal implícita , por lo que no cuenta como la misma tabla que está actualizando.

... pero ten cuidado con el optimizador de consultas

Sin embargo, tenga en cuenta que, a partir de MySQL 5.7.6 y posteriores, el optimizador puede optimizar la subconsulta y aún le puede dar el error. Afortunadamente, la variable optimizer_switch puede usarse para desactivar este comportamiento; aunque no podría recomendar hacer esto como algo más que una solución a corto plazo, o para pequeñas tareas puntuales.

SET optimizer_switch = ''derived_merge=off'';

Gracias a Peter V. Mørch por este consejo en los comentarios.

Ejemplo de técnica fue de Baron Schwartz, originalmente publicado en Nabble , parafraseado y extendido aquí.

Answer 13

NexusRex proporcionó una muy buena solución para eliminar con unirse de la misma tabla.

Si haces esto:

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
)

vas a obtener un error

Pero si envuelves la condición en una selección más:

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.id
) AS c
)

Haría lo correcto !!

Explicación: el optimizador de consultas realiza una optimización de fusión derivada para la primera consulta (lo que hace que falle con el error), pero la segunda consulta no califica para la optimización de fusión derivada . Por lo tanto, el optimizador se ve obligado a ejecutar la subconsulta primero.

Answer 14

DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
) AS c
)