Link to home
Start Free TrialLog in
Avatar of PSIUnit
PSIUnit

asked on

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What's wrong with this code?  I'm getting the above error message.  I'm trying to delete all records that are duplicates except for the one with the largest date.  


DELETE FROM NIPRLicenseInfo
WHERE (NPN IN
(SELECT NPN, State, LicenseID
FROM NIPRLicenseInfo
GROUP BY NPN, State, LicenseID
HAVING (COUNT(NPN) > 1))
AND NOT [ExpirationDate] IN (
SELECT MAX([ExpirationDate]) as MaxOfExpire
FROM NIPRLicenseInfo
GROUP BY NPN, State, LicenseID
HAVING ((COUNT(NPN))>1)))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PSIUnit
PSIUnit

ASKER

You are so awesome.  Why didn't I think of that?  I think I always try to overcomplicate everything...  Thanks!
What's wrong is:

WHERE (NPN IN
(SELECT NPN, State, LicenseID
FROM NIPRLicenseInfo

A SELECT Statment inside the IN must only return 1 field.

You need:
WHERE (NPN IN
(SELECT NPN
FROM...)
Avatar of PSIUnit

ASKER

Ok - that makes sense.  Thanks!