sql - update - No puede especificar la tabla de destino para la actualización en la cláusula FROM
update sql server ejemplo (9)
Como referencia, también puede usar Variables Mysql para guardar resultados temporales, por ejemplo:
SET @v1 := (SELECT ... );
UPDATE ... SET ... WHERE x=@v1;
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?
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)
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 tableen 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áusulaWHEREpara limitar los resultados, también.
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 = ?
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.
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
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 dataDesde 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);
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
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é