Solved DELETE+INNERJOIN on two fields (none is primary key): missing records (1 Viewer)

Etxezarreta

Member
Local time
Today, 17:00
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.

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
'
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:
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)"
I don't understand at all, any tip would be welcome.
Many thanks in advance.
Etxe.
 

Attachments

  • DELETE INNER JOIN TWO FIELDS.zip
    715.9 KB · Views: 119

Etxezarreta

Member
Local time
Today, 17:00
Joined
Apr 13, 2020
Messages
175
I just found a solution:
Code:
            str_SQL_finale2 = "DELETE DISTINCTROW PROJETS_t_AffectationProjetA_Contacts_temp.*" _
                            & " 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)" _
                            & " AND (PROJETS_t_AffectationProjetA_Contacts_temp.FK_ID_contact= PROJETS_t_AffectationProjetA_Contacts.FK_ID_contact)"

Solved.
 

Users who are viewing this thread

Top Bottom