from in to exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

from in to exists

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 04:46:08

Dear all,
i'm trying to replace in with exists...
i've changed the query but still i'm getting error.
please let me know where i'm missing

DELETE FROM table25 WHERE Col6='SET' AND Col1 IN (SELECT s.Col1 FROM Table24 s
WHERE Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

DELETE FROM table25 r WHERE r.Col6='SET' AND exists (SELECT s.Col1 FROM Table24 s
WHERE r.col1=s.col2 and Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 04:48:23

[code]DELETE u
FROM Table25 AS u
WHERE u.Col6 = 'SET'
AND EXISTS (SELECT * FROM Table24 AS t WHERE t.Col1 = u.Col1 AND t.Col2 = 'some_data1' AND t.Col3 = 'some_data2' AND t.Col4 = 'somedata3')[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 04:49:10

[code]DELETE u
FROM Table25 AS u
INNER JOIN Table24 AS t ON t.Col1 = u.Col1
WHERE u.Col6 = 'SET'
AND t.Col2 = 'some_data1'
AND t.Col3 = 'some_data2'
AND t.Col4 = 'somedata3'[/code]

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-06 : 04:49:12

DELETE FROM table25 r WHERE r.Col6='SET' AND exists (SELECT * FROM Table24 s
WHERE r.col1=s.s.Col1 and Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-06 : 04:49:56

.... oh why even bother...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 05:02:07

thank you Peso and spirit.....
actually which can i replace for best performance....
can i go for exists or for join?

spirit1 now my bother is regarding the above.....(just for fun..don't be serious)

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 05:06:24

dear peso,
which one is more efficient can i go for join or exists?

Vinod
Even you learn 1%, Learn it with 100% confidence.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-06 : 05:12:09

no no.. i meant why even bother posting since peso always beats me to it

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-12-06 : 05:40:21

Dear Peso,
i'm using the exists query and replacing *
with col1,col2,col3,col4

am i correct ??? as i'm trying to tune the query
spirit this is a great chance to beat peso

Vinod
Even you learn 1%, Learn it with 100% confidence.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 06:34:55

Why would you select some columns from EXISTS predicate?
You only want to see if there are some matching records! The EXISTS never returns RECORDS, it returns TRUE or FALSE only.

Try both scenarios (EXISTS vs INNER JOIN) and compare execution plans.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources