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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

SQL - IN statement

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
Star79
Asked:
Star79
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Star79Author Commented:
how is the IN different from EXISTS
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
PortletPaulCommented:
>>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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now