multimatch query not working

[diegomarino]

hi, i use this query to delete records that are not present in the second table

DELETE *
FROM ISCRITTIOCFNUOVI
WHERE IScrittiocfnuovi.id IN
(SELECT IScrittiocfnuovi.id
FROM IScrittiocfnuovi LEFT JOIN IscrittiOCFTemp ON (IScrittiocfnuovi.nome = IscrittiOCFTemp.nome) AND (IscrittiOCFTemp.cognome = IScrittiocfnuovi.cognome) AND (IscrittiOCFTemp.data_nascita = IScrittiocfnuovi.data_nascita) AND (IscrittiOCFTemp.luogo_nascita = IScrittiocfnuovi.luogo_nascita)
WHERE IscrittiOCFTemp.nome is null AND IscrittiOCFTemp.cognome is null AND IscrittiOCFTemp.data_nascita is null AND IscrittiOCFTemp.luogo_nascita is null);

but it finds records that actually are in both table.
Where is the problem?


thanks

[Welshgasman]

I would expect you should be using NOT IN ?

[CJ_London]

You also need to alias IScrittiocfnuovi in the sub query

[diegomarino]

i got the problem, i think the database was corrupting.
I exported everything and the query works fine now.
Do you think i have to do something more or my db is fine now?

[CJ_London]

Do you think i have to do something more or my db is fine now?

impossible to say without knowing what changes you have actually done, what your data actually is and what caused the corruption. db’s usually corrupt because of something you have done or the setup is wrong - such as not split and you have multiple users)

[diegomarino]

well, maybe i messed up something but now it's fine.
I'll see if everything is fine on the medium period
thanks

[Welshgasman]

I do not see how, you say not in other table, but then compare sub query with IN ?

Doing the opposite I would have thought?

[diegomarino]

This is my new delete query

DELETE *FROM ISCRITTIOCFNUOVI
WHERE IScrittiocfnuovi.id IN
(SELECT id
FROM qryOCFNuoviNPTemp
);

The base query (qryOCFNuoviNPTemp) is this one

SELECT ISCRITTIOCFNUOVI.id, ISCRITTIOCFNUOVI.Nome, ISCRITTIOCFNUOVI.Cognome, ISCRITTIOCFNUOVI.Data_nascita, ISCRITTIOCFNUOVI.Luogo_nascitaFROM ISCRITTIOCFNUOVI LEFT JOIN iscrittiocftemp ON (ISCRITTIOCFNUOVI.Nome = ISCRITTIOCFTEMP.Nome) AND (ISCRITTIOCFNUOVI.Cognome = ISCRITTIOCFTEMP.Cognome) AND (ISCRITTIOCFNUOVI.Data_nascita = ISCRITTIOCFTEMP.Data_nascita) AND (ISCRITTIOCFNUOVI.Luogo_nascita = ISCRITTIOCFTEMP.Luogo_nascita)
WHERE ISCRITTIOCFTEMP.Nome IS NULL
AND ISCRITTIOCFTEMP.cogNome IS NULL
AND ISCRITTIOCFTEMP.data_nascita IS NULL
AND ISCRITTIOCFTEMP.luogo_nascita IS NULL;

I had to make the base query separately cause if i put the sql of the base directly in the delete query sometimes i got records that should be not in, sometimes it works fine.
How is this possible?
I made new tables and copied records in them, after exported in another DB all the tables (before i exported my front end)

[Welshgasman]

How is this possible?

You do not know your data? SQL can only do what you instruct.

I would always start with a Select to see I get the expected data, and only then change to Append/Delete etc.

[diegomarino]

i DO know my data, and i always start with a select query.
But before i got different results without any clear reason, now it seems fixed after exportation, but i'll keep an eye on that
thanks

[diegomarino]

it was a problem of the db, i exported everything in another and now it works.