Delete from Nested Select

[jnavarro]

Can anyone help me. I cannot get this sql statment to excute. What I am trying to do is select record from record set and delete those records in the table


DELETE tblAI
FROM tblAI
WHERE(SELECT Div, Account, CorrespondType, COUNT (Account) AS Inquiries, MAX(MediaDate) AS MediaDate
FROM tblAI
WHERE DateResolve IS NULL
GROUP BY Div, Account, CorrespondType
HAVING COUNT(Account) > 1) DupAccount INNER JOIN tblAI ON tblAI.Div = DupAccount.Div AND tblAI.Account = DupAccount.Account AND tblAI.MediaDate = DupAccount.MediaDate AND
tblAI.CorrespondType = DupAccount.CorrespondType eneral_CP1_CI_AS = tblAI.CorrespondType


Thanks

 

[amrita418]

Try this:

DELETE 	tblAI
FROM 	tblAI,
	(SELECT 	Div, 
			Account, 
			CorrespondType, 
			MAX(MediaDate) AS MediaDate
         FROM   	tblAI
      	 WHERE  	DateResolve IS NULL
      	 GROUP BY 	Div, Account, CorrespondType
      	 HAVING 	COUNT(Account) > 1) DupAccount
where    tblAI.Div = DupAccount.Div 
	 AND tblAI.Account = DupAccount.Account 
         AND tblAI.MediaDate = DupAccount.MediaDate 
	 AND tblAI.CorrespondType = DupAccount.CorrespondType

Regards,
AA

 

[jnavarro]

Thanks it worked