Solved

SQL - IN statement

Posted on 2013-10-31
5
289 Views
Last Modified: 2013-11-04
Hello ,
I have below query
 
declare @mcdid table (
 comparevalue varchar(20)
) 
insert into @mcdid(comparevalue)
SELECT CDID FROM PerDiemFormulary WHERE FacID=7
delete from tableX where ndc in(select * from @mcdid )

Open in new window


The above does not work, the in statement is not working and it is not deleting the rows from tablex which are in @mcdid.
Please help.
0
Comment
Question by:Star79
  • 3
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39614598
>delete from tableX where ndc in(select * from @mcdid )

In your subquery, delete the * (which means all columns) and replace it with DISTINCT ndc, so that the main query and subquery can do an apples-to-apples, ndc-to-ndc comparison.

delete from tableX where ndc in(select DISTINCT ndc from @mcdid )
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39614608
although in your case, @mcdid only has column comparevalue, so assuming that ndc and comparevalue are the same...

DELETE FROM TableX WHERE ndc IN (SELECT DISTINCT comparevalue FROM @mcdid)
0
 

Author Comment

by:Star79
ID: 39614999
how is the IN different from EXISTS
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39615025
it's essentially the same, as it is a condition where a single value must match the return set of a subquery.   Although since EXISTS measures the existance of a value, it's somewhat faster.

IN can be done manually as a simple list though..
SELECT *
FROM MyFruit
WHERE FruitType IN ('banana', 'orange', 'grape')

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39621341
>>how is the IN different from EXISTS

they are quite different "under the hood"

the use of IN() is really a "syntax shortcut", using Jim's example:

          WHERE FruitType IN ('banana', 'orange', 'grape')

is just a shortcut for the following (and is the EXACT equivalent of):

          WHERE ( FruitType ='banana' OR FruitType ='orange' OR FruitType ='grape' )

so the contents of the parentheses is a series of OR statements within the where clause.

---------
"under the hood" an EXISTS() structure is a "semi-join", i.e. it is similar to a join but isn't quite the same, but due to this close relationship with joins using EXISTS() is often efficient.

--------
note, when using IN() with a limited range of values than it is just as efficient as EXISTS() and can be indistinguishable in performance terms. However with very large lists of variables performance of IN() can degrade quite markedly,
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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