Solved

SQL - IN statement

Posted on 2013-10-31
5
285 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
Comment Utility
>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
Comment Utility
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
Comment Utility
how is the IN different from EXISTS
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
>>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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now