Início / dba / Perguntas / 33406
Accepted
Pietro
Asked: 2013-01-24 06:08:52 +0800 CST

Problema com consultas UPDATE aninhadas

  • 772

Não consigo fazer uma consulta de atualização funcionar. Como primeiro passo, o seguinte funciona corretamente: 

UPDATE Tab1  
SET Tab1.a = '3'
WHERE
    Tab1.id IN ( 123, 456 );

onde id é a chave primária de Tab1.
No entanto, quando adiciono um select para obter o conjunto de valores que Tab1.id deve conter, recebo o seguinte erro do MySQL:

Código de erro: 1093
Você não pode especificar a tabela de destino 'Tab1' para atualização na cláusula FROM.

Esta é a consulta completa: 

UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

Não consigo ver como corrigir esse erro.
Sou forçado a dividir esta consulta em duas?

Obrigada!

mysql update

3 respostas

  1. Best Answer

    Tentar

    UPDATE Tab1 JOIN Tab2 ON Tab1.b = Tab2.b
SET Tab1.a = '3'
WHERE Tab1.c = '4'
;

    Verifique a documentação para obter mais informações (procure por 'sintaxe de tabelas múltiplas'). Um ponto importante é:

    Se você usar uma instrução UPDATE de várias tabelas envolvendo tabelas InnoDB para as quais existem restrições de chave estrangeira, o otimizador do MySQL pode processar tabelas em uma ordem diferente daquela de seu relacionamento pai/filho. Nesse caso, a instrução falha e é revertida. Em vez disso, atualize uma única tabela e conte com os recursos ON UPDATE que o InnoDB fornece para fazer com que as outras tabelas sejam modificadas de acordo. Consulte a Seção 14.3.5.4, “Restrições FOREIGN KEY”.

    Você pode verificar um exemplo semelhante no SQLFiddle .

    • 5

  2. INFORMAÇÃO SUPLEMENTAR

    Especificar UPDATEs usando subconsultas pode ser um negócio perigoso.

    Em 21 de fevereiro de 2011, alguém fez esta pergunta: Problema com a subconsulta do MySQL .

    Fiz uma pequena pesquisa e descobri algo perturbador: durante a otimização de uma consulta que envolve uma subconsulta contra si mesma, é perfeitamente possível que linhas desapareçam intermitentemente .

    Portanto, considere a mensagem de erro

    Código de erro: 1093
    Você não pode especificar a tabela de destino 'Tab1' para atualização na cláusula FROM.

    um aviso amigável do MySQL para não escrever consultas com esse estilo

    UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT Tab1.id 
            FROM Tab1, Tab2 
            WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
        );

    A resposta de @dezso (obtém um +1), que emprega o uso de umUPDATE JOIN, é mais palatável para o MySQL Query Optimizer

    • 2

  3. Nesse caso, a resposta de @dezso é a escolha certa; no entanto, há outra abordagem que às vezes é útil quando confrontada com uma atualização que faz referência à mesma tabela.

    Quando você precisa atualizar uma tabela com base em algo que deseja derivar da própria tabela, pode persuadir o otimizador de forma bastante consistente a materializar uma subconsulta não correlacionada em uma tabela temporária implícita antes de processar a consulta externa.

    UPDATE Tab1  
    SET Tab1.a = '3'
    WHERE
        Tab1.id IN ( 
            SELECT * FROM (                            /* add this line */ 
                SELECT Tab1.id 
                FROM Tab1, Tab2 
                WHERE Tab1.b = Tab2.b AND Tab1.c = '4'
            ) hack                                     /* and this line */
        );

    Onde hackestá um alias para essa tabela temporária. Sem algum tipo de nome ali (e um opcional ASantes do nome):

    ERROR 1248 (42000): Every derived table must have its own alias

    Na verdade, esse é um comportamento documentado e é bastante útil quando não há uma solução mais apropriada, como a junção.

    Aqui, o resultado da subconsulta na cláusula FROM é armazenado como uma tabela temporária, portanto, as linhas relevantes tjá foram selecionadas no momento em que a atualização tocorre.

    http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html

    • 1

relate perguntas