Solved

SQL - IN statement

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

860 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