sql - update - No puede especificar la tabla de destino para la actualización en la cláusula FROM

update sql server ejemplo

Question

Tengo una tabla mysql simple:

CREATE TABLE IF NOT EXISTS `pers` (
  `persID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) NOT NULL,
  `gehalt` int(11) NOT NULL,
  `chefID` int(11) DEFAULT NULL,
  PRIMARY KEY (`persID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES
(1, ''blb'', 1000, 3),
(2, ''as'', 1000, 3),
(3, ''chef'', 1040, NULL);

Intenté ejecutar la siguiente actualización, pero solo obtengo el error 1093:

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR gehalt < 
(SELECT (
    SELECT MAX(gehalt * 1.05) 
    FROM pers MA 
    WHERE MA.chefID = MA.chefID) 
    AS _pers
))

Busqué el error y lo encontré en mysql en la página http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html , pero no me ayuda.

¿Qué debo hacer para corregir la consulta SQL?

Answer 1

Como referencia, también puede usar Variables Mysql para guardar resultados temporales, por ejemplo:

SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

Answer 2

El Enfoque publicado por BlueRaja es lento. Lo modifiqué mientras lo usaba para eliminar duplicados de la tabla. En caso de que ayude a cualquiera con tablas grandes. Consulta original.

delete from table where id not in (select min(id) from table group by field 2)

Esto está tomando más tiempo:

DELETE FROM table where ID NOT IN(
  SELECT MIN(t.Id) from (select Id,field2 from table) AS t GROUP BY field2)

Solución más rápida

DELETE FROM table where ID NOT IN(
   SELECT x.Id from (SELECT MIN(Id) as Id from table GROUP BY field2) AS t)

Answer 3

El problema es que MySQL, por cualquier razón inane, no te permite escribir consultas como esta:

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM myTable
    INNER JOIN ...
)

Es decir, si está realizando una UPDATE / INSERT / DELETE en una tabla, no puede hacer referencia a esa tabla en una consulta interna (sin embargo, puede hacer referencia a un campo de esa tabla externa ...)

La solución es reemplazar la instancia de myTable en la myTable con (SELECT * FROM myTable) , como esta

UPDATE myTable
SET myTable.A =
(
    SELECT B
    FROM (SELECT * FROM myTable) AS something
    INNER JOIN ...
)

Aparentemente, esto hace que los campos necesarios se copien implícitamente en una tabla temporal, por lo que está permitido.

Encontré esta solución here . Una nota de ese artículo:

No solo desea seleccionar la SELECT * FROM table en la subconsulta en la vida real; Solo quería mantener los ejemplos simples. En realidad, solo debe seleccionar las columnas que necesita en la consulta más interna y agregar una buena cláusula WHERE para limitar los resultados, también.

Answer 4

En Mysql, no puede actualizar una tabla por subconsulta de la misma tabla.

Puede separar la consulta en dos partes, o hacer

UPDATE TABLE_A AS A INNER JOIN TABLE_A AS B ON A.field1 = B.field1 SET field2 = ?

Answer 5

Es bastante simple Por ejemplo, en lugar de escribir:

INSERT INTO x (id, parent_id, code) VALUES (
    NULL,
    (SELECT id FROM x WHERE code=''AAA''),
    ''BBB''
);

Deberías escribir

INSERT INTO x (id, parent_id, code)
VALUES (
    NULL,
    (SELECT t.id FROM (SELECT id, code FROM x) t WHERE t.code=''AAA''),
    ''BBB''
);

o similar.

Answer 6

Hacer una tabla temporal (tempP) desde una subconsulta

UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE P.persID IN (
    SELECT tempP.tempId
    FROM (
        SELECT persID as tempId
        FROM pers P
        WHERE
            P.chefID IS NOT NULL OR gehalt < 
                (SELECT (
                    SELECT MAX(gehalt * 1.05) 
                    FROM pers MA 
                    WHERE MA.chefID = MA.chefID) 
                    AS _pers
                )
    ) AS tempP
)

He introducido un nombre separado (alias) y le he dado un nombre nuevo a la columna ''persID'' para la tabla temporal

Answer 7

MariaDB ha levantado esto a partir de 10.3.x (ambos para DELETE y UPDATE ):

ACTUALIZACIÓN - Declaraciones con el mismo origen y destino

Desde MariaDB 10.3.2, las declaraciones de ACTUALIZACIÓN pueden tener el mismo origen y destino.

Hasta MariaDB 10.3.1, la siguiente declaración de ACTUALIZACIÓN no funcionaría:

UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);
  ERROR 1093 (HY000): Table ''t1'' is specified twice, 
  both as a target for ''UPDATE'' and as a separate source for data

Desde MariaDB 10.3.2, la sentencia se ejecuta con éxito:

UPDATE t1 SET c1=c1+1 WHERE c2=(SELECT MAX(c2) FROM t1);

ELIMINAR - Misma tabla de origen y destino

Hasta MariaDB 10.3.1, no era posible eliminar de una tabla con el mismo origen y destino. Desde MariaDB 10.3.1, esto es ahora posible. Por ejemplo:

DELETE FROM t1 WHERE c1 IN (SELECT b.c1 FROM t1 b WHERE b.c2=0);

DBFiddle MariaDB 10.2 - Error

DBFiddle MariaDB 10.3 - Éxito

Answer 8

Puedes hacer esto en tres pasos:

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

o

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId

Answer 9

Si está intentando leer fieldA de la tabla A y guardarlo en fieldB en la misma tabla, cuando fieldc = fieldd puede considerar esto.

UPDATE tableA,
    tableA AS tableA_1 
SET 
    tableA.fieldB= tableA_1.filedA
WHERE
    (((tableA.conditionFild) = ''condition'')
        AND ((tableA.fieldc) = tableA_1.fieldd));

El código anterior copia el valor del campo A al campo B cuando el campo condición cumple su condición. esto también funciona en ADO (por ejemplo, acceso)

fuente: probé