Solved

SQL - IN statement

Posted on 2013-10-31
5
292 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2013 - Overlapping reports 2 38
sql 2008 how to table join 2 30
How can I find this data? 3 23
SQL Server Express or Standard? 5 18
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

679 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