Início / dba / Perguntas / 342404
Accepted
ReynierPM
Asked: 2024-09-16 23:31:36 +0800 CST

Como posso escrever com segurança uma instrução UPDATE para atualizar valores ausentes da TABELA2?

  • 772

Estou corrigindo alguns dados incorretos e estou procurando uma maneira mais segura de atualizar uma determinada coluna quando o valor não existe mais na tabela relacionada.

Dê uma olhada no exemplo a seguir ( violino aqui ):

-- SET sql_mode = '';

create table if not exists table1
(
    id                           int auto_increment primary key,
    table2_id int
);

create table if not exists table2
(
    id                           int auto_increment primary key
);


INSERT INTO table1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
INSERT INTO table2 VALUES(1), (2);

SELECT * FROM table1;
SELECT * FROM table2;

SELECT * FROM table1 LEFT JOIN table2 ON table1.table2_id = table2.id;

a última consulta acima retornará apenas 2 linhas porque table2não tem os IDs: 3, 4, 5. Então preciso escrever uma ATUALIZAÇÃO para a tabela1 para definir os IDs: 3, 4, 5 para NULL.

Posso ter algumas ideias?

mysql

2 respostas

  1. O que há de errado com:

    update table1 set id = null where table2_id not in (select id from table2);

    (observe que NOT INesta é uma opção segura somente se a coluna da subconsulta não for anulável)

    Eu também sugeriria adicionar uma restrição de chave estrangeira depois que os dados forem organizados, para evitar que os dados ruins voltem a aparecer.

    • 1
  2. Best Answer

    Eu sugeriria usar NOT EXISTS, geralmente será mais eficiente do que usarLEFT JOIN

    UPDATE table1 t1 
SET t1.table2_id = null 
WHERE NOT EXISTS (SELECT 1 from table2 t2 WHERE t2.id=t1.table2_id)

    Veja o exemplo

    LEFT JOINé outra opção.

    UPDATE table1 t1 
LEFT JOIN table2 t2 ON  t1.table2_id = t2.id
SET t1.table2_id = NULL 
WHERE t2.id IS NULL

    Veja o exemplo

    Editar A resposta foi alterada de recomendar LEFT JOINpara recomendarNOT EXISTS

    Teste detalhado pode ser encontrado no MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL

    • 1

relate perguntas