Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
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
Medium Priority
?
471 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20385463
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
ID: 20385496
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
ID: 20385508
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
ID: 20385524
Ok - that makes sense.  Thanks!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.
Suggested Courses

564 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