MYSQL Insert into com Where not exists

Question

Staff I have the following situation, I have 2 tables, I need to insert in table B data that are in table A, however, should be checked if the data that are in table A already exist in table B, if there should be disregard, can only enter if there is no data.

I am trying with the following query:

INSERT INTO TABELA B (
n_notif_esus_ve,
data_notificacao,
data_inicio_sintomas,
exames,
nome,
nome_mae,
sexo,
data_nasc,
idade,
raca_cor,
endereco,
bairro,
telefone,
cpf_api,
cns_api
)
SELECT
n_notificacao,
data_notificacao,
data_inicio_sintomas,
tipo_teste,
nome,
nome_mae,
sexo,
data_nasc,
idade,
raca_cor,
endereco,
bairro,
telefone,
cpf,
cns
FROM
TABELA A
WHERE
(SELECT * FROM TABELA A y
WHERE NOT EXISTS (SELECT * FROM TABELA B x WHERE y.nome = x.nome AND y.data_nasc = x.data_nasc
));

I separately tested the Insert and the query that contains the Where not exists, both of which work, but when I put Where not exists as a condition, I get the following error:

Error Code: 1241. Operand should contain 1 column(s)

I appreciate anyone who can help, obg.

Answer 1

SOLUTION:

The solution was to concatenate the fields in the Where, was passing 2 fields, concatenei and compared later. It follows below:

INSERT INTO table two (
    campos
)
SELECT DISTINCT
    campos
FROM
    table one a
WHERE NOT EXISTS 
    (SELECT DISTINCT * FROM table two b 
WHERE
    CASE
        WHEN (a.nome IS NOT NULL AND a.data_nasc IS NOT NULL AND a.cpf IS NOT NULL AND b.nome IS NOT NULL and b.data_nasc IS NOT NULL and b.cpf_api IS NOT NULL)
            THEN concat(a.nome, a.data_nasc, a.cpf) = concat(b.nome, b.data_nasc, b.cpf_api)
    END);

Answer 2

Eai dear beauty?

I’ve come across a similar situation, I don’t have the source to look at how it was resolved at the time.. more in the SQL part, I remember something like this solved :

select if (
  exists ( select * from nota_geral where tipo_notafiscal = ? and ide_mod = 65 ), 'EXISTE', 'NÃO EXISTE'
);

Try to adapt this if in exchange for the CLAUSE WHERE NOT EXISTS ... If there is any problem you can post here...

I hope it helped!

Answer 3

This error occurs because of your sub query that is returning a column. See if free IN solves: Example:

WHERE NOME_CAMPO IN (SELECT .... )