Etxezarreta
Member
- Local time
- Today, 19:46
- Joined
- Apr 13, 2020
- Messages
- 175
Hello everyone,
I need to delete from "PROJETS_t_AffectationProjetA_Contacts_temp" (7 rows) the records for which two fields match with the fields of "PROJETS_t_AffectationProjetA_Contacts" (2 rows).
When there is no matching at all, it works fine (7 records appear)
The problem occurs when there is one matching: instead of deleting only one rox,i t delete as many rows as there are in PROJETS_t_AffectationProjetA_Contacts (2 in our case).
It drives me nuts.
Here is the code.
I tried with only one IN in a time: it works with the field FK_ID_contact but with the field FK_ID_projet, if there is only one matching, it clears the table:
I don't understand at all, any tip would be welcome.
Many thanks in advance.
Etxe.
I need to delete from "PROJETS_t_AffectationProjetA_Contacts_temp" (7 rows) the records for which two fields match with the fields of "PROJETS_t_AffectationProjetA_Contacts" (2 rows).
When there is no matching at all, it works fine (7 records appear)
The problem occurs when there is one matching: instead of deleting only one rox,i t delete as many rows as there are in PROJETS_t_AffectationProjetA_Contacts (2 in our case).
It drives me nuts.
Here is the code.
Code:
str_SQL_finale2 = " DELETE DISTINCTROW PROJETS_t_AffectationProjetA_Contacts_temp.* FROM PROJETS_t_AffectationProjetA_Contacts_temp" _
& " WHERE PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact in" _
& " (SELECT PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact" _
& " FROM PROJETS_t_AffectationProjetA_Contacts_temp" _
& " INNER JOIN PROJETS_t_AffectationProjetA_Contacts" _
& " ON PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact= PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)" _
& " AND PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet in" _
& " (SELECT PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet" _
& " FROM PROJETS_t_AffectationProjetA_Contacts_temp" _
& " INNER JOIN PROJETS_t_AffectationProjetA_Contacts" _
& " ON PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet = PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet)"
objConnection.Execute (str_SQL_finale2)
Me.Refresh
'
Code:
str_SQL_finale2 = "DELETE DISTINCTROW PROJETS_t_AffectationProjetA_Contacts_temp.* FROM PROJETS_t_AffectationProjetA_Contacts_temp" _
& " WHERE PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet in" _
& " (SELECT PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet" _
& " FROM PROJETS_t_AffectationProjetA_Contacts_temp" _
& " INNER JOIN PROJETS_t_AffectationProjetA_Contacts" _
& " ON PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_projet = PROJETS_t_AffectationProjetA_Contacts.FK_ID_projet)"
Many thanks in advance.
Etxe.