SOQL Query Error: Compile Error: unexpected token: 'SELECT' at line x column y

Question

I am trying to gather Account record Ids so I can re-parent some Contact records. I only need to gather records where a certain Account field matches the same value as the corresponding Contact field.

I keep getting an error for my SOQL statement.

Error: Compile Error: unexpected token: 'SELECT' at line 10 column 111

Why can I not gather a list of Accounts based on a field value being in a list of Contacts? Please let me know where I took a wrong turn.

trigger reparentcontacts on Contact (after insert) {

List<Contact> unparentedcontacts = new List<Contact>();
for (Contact i : Trigger.new) {
    if (i.recordtypeid == 'xxxxxxxxxxxx') {
        unparentedcontacts.add(i); 
    }   
}

    List<Account> parentaccounts = [SELECT id, D_U_N_S_Number__c FROM Account WHERE D_U_N_S_Number__c IN :(SELECT id, DUNS_Number__c FROM Contact WHERE id IN :unparentedcontacts)];

    for(Contact j : unparentedcontacts) {
    j.Account = parentaccounts.id;
    }

    update unparentedcontacts;


}

Answer 1

You can capture all the necessary DUNS_Number__c in a set and use the set to fetch your accounts

//rest of your code

set<String> setDNSNo = new set<String>();
for(Contact c :SELECT id, DUNS_Number__c 
                                     FROM Contact 
                                     WHERE id IN :unparentedcontacts){
    setDNSNo.add(c.DUNS_Number__c);
}
List<Account> parentaccounts = [SELECT id, D_U_N_S_Number__c 
                                           FROM Account 
                                           WHERE D_U_N_S_Number__c IN :setDNSNo];