Solved

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

Posted on 2007-11-30
4
459 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:PSIUnit
  • 2
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
here we go:

DELETE n
FROM NIPRLicenseInfo n
WHERE EXISTS (
   SELECT NULL 
   FROM NIPRLicenseInfo i
   WHERE i.NPN = n.NPN
     AND i.ExpirationDate > n.ExpirationDate
  )

Open in new window

0
 

Author Comment

by:PSIUnit
Comment Utility
You are so awesome.  Why didn't I think of that?  I think I always try to overcomplicate everything...  Thanks!
0
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
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...)
0
 

Author Comment

by:PSIUnit
Comment Utility
Ok - that makes sense.  Thanks!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now