not in operator with multiple columns

Question

not in operator with multiple columns

thava

SSCrazy

Points: 2275
More actions
July 13, 2010 at 7:54 am

#224700

DELETE
FROM BatchStock
WHERE PurId = @PurID AND DocRef = @DocRef
AND (slno,itemid) NOT IN (SELECT slno,itemid FROM #Tmp)
is it possible doing like this
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

thava (7/13/2010)
DELETE FROM BatchStock WHERE PurId = @PurID AND DocRef = @DocRef AND (slno,itemid) NOT IN (SELECT slno,itemid FROM #Tmp)
is it possible doing like this

No! It is not possible.

However there are other ways to achieve what you want:

1. If you want to check for a specific combination of slno & itemid

then you can use the following:

DELETE FROM BatchStock

WHERE PurId = @PurID

AND DocRef = @DocRef

AND NOT EXISTS (SELECT 1 FROM #Tmp t

WHERE t.slno = BatchStock.slno

AND t.itemid = BatchStock.itemid)

2. If you want to make sure that slno and/or itemid are not in the list, use this:

DELETE FROM BatchStock

WHERE PurId = @PurID

AND DocRef = @DocRef

AND NOT EXISTS (SELECT 1 FROM #Tmp t

WHERE t.slno = BatchStock.slno

OR t.itemid = BatchStock.itemid)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

thava SSCrazy Points: 2275 More actions · Answer 2

thanks for your immediate response

is this query is correct

DELETE BatchStock

FROM #tmp t

WHERE BatchStock.PurId = @PurID AND BatchStock.DocRef = @DocRef

AND NOT (BatchStock.slno =t.slno AND BatchStock.itemid = t.itemid)

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

thava (7/13/2010)
thanks for your immediate response
is this query is correct
DELETE BatchStock
FROM #tmp t
WHERE BatchStock.PurId = @PurID AND BatchStock.DocRef = @DocRef
AND NOT (BatchStock.slno =t.slno AND BatchStock.itemid = t.itemid)

What you mean by "correct"?

If it doesn't compile or run it is incorrect for sure.

If it does compile and run with no errors, does it do what you want?

If it does - it is correct, if no - incorrect.

In order to answer your question I need to know what you are trying/want to do.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Have you tried a left join?

FROM BatchStock b

LEFT JOIN #Tmp t ON t.slno = b.slno AND t.itemid = b.itemid

WHERE b.PurId = @PurID AND b.DocRef = @DocRef

AND t.slno IS NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

thava SSCrazy Points: 2275 More actions · Answer 5

sorry, i think i am not clear there

i want to delete the records in the batchstock table if the combination of the slno and itemid not available in the #tmp table

like if the batchstock table contains

slno itemid

1 2

2 2

3 4

but the #tmp contatins

slno itemid

1 2

2 3

3 4

now the combination 2,2 doesnot lie in the #tmp so i want to delete that record, i think i am clear now the query i given is working for me

i want to clarify that whether it is wright approach are not

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

thava SSCrazy Points: 2275 More actions · Answer 6

wow that's great simple and easy chris

i got it thanks for your reply

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

thava SSCrazy Points: 2275 More actions · Answer 7

great

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

thava (7/13/2010)
wow that's great simple and easy chris
i got it thanks for your reply

Gosh :blush: you're welcome, thanks for the feedback!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden