sql - erro - you can't specify target table for update in from clause update




Você não pode especificar a tabela de destino para atualização na cláusula FROM (6)

É bem simples. Por exemplo, em vez de escrever:

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

você deveria escrever

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

ou similar.

Eu tenho uma tabela simples de mysql:

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);

Eu tentei executar a seguinte atualização, mas recebo apenas o erro 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
))

Eu procurei o erro e encontrei a partir da página mysql seguinte http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html , mas isso não me ajuda.

O que devo fazer para corrigir a consulta sql?


A Abordagem postada pelo BlueRaja é lenta Eu a modifiquei como estava usando para excluir duplicatas da tabela. Caso ajude alguém com tabelas grandes Consulta Original

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

Isso leva mais tempo:

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

Solução mais 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)

Crie uma tabela temporária (tempP) de uma 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
)

Eu introduzi um nome separado (apelido) e dou um novo nome para a coluna 'persid' para tabela temporária


MariaDB elevou isso a partir de 10.3.x (tanto para DELETE e UPDATE ):

UPDATE - declarações com a mesma fonte e destino

No MariaDB 10.3.2, as instruções UPDATE podem ter a mesma origem e destino.

Até o MariaDB 10.3.1, a seguinte instrução UPDATE não funcionaria:

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

Do MariaDB 10.3.2, a instrução é executada com sucesso:

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

DELETE - Mesma Fonte e Tabela de Destino

Até o MariaDB 10.3.1, a exclusão de uma tabela com a mesma origem e destino não era possível. Do MariaDB 10.3.1, isso agora é possível. Por exemplo:

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

DBFiddle MariaDB 10.2 - Erro

DBFiddle MariaDB 10.3 - Sucesso


O problema é que o MySQL, por alguma razão vazia, não permite escrever consultas como esta:

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

Ou seja, se você estiver fazendo um UPDATE / INSERT / DELETE em uma tabela, não poderá fazer referência a essa tabela em uma consulta interna (no entanto, você pode referenciar um campo dessa tabela externa ...)

A solução é substituir a instância de myTable na subconsulta com (SELECT * FROM myTable) , assim

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

Isso aparentemente faz com que os campos necessários sejam implicitamente copiados para uma tabela temporária, portanto, é permitido.

Eu encontrei esta solução here . Uma nota desse artigo:

Você não quer apenas a SELECT * FROM table na subconsulta na vida real; Eu só queria manter os exemplos simples. Na realidade, você só deve selecionar as colunas necessárias nessa consulta mais interna e adicionar uma boa cláusula WHERE para limitar os resultados também.


Se você está tentando ler fieldA da tableA e salvá-lo no campoB na mesma tabela, quando fieldc = fieldd você pode querer considerar isto.

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

O código acima copia o valor do campo A para o campo B quando o campo de condições satisfizer sua condição. isso também funciona no ADO (por exemplo, acesso)

fonte: tentei





mysql-error-1093