SQL Question

Question

Post reply

SQL Question

Viewing 6 posts - 16 through 20 (of 20 total)

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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 1

Mister Ken (4/14/2010)

I saw a coworker's snippet once, it reminded me vaguely of a similar idea. The statement below works.

UPDATE t1

SET t1.ID = NULL

FROM dbo.table1 t1

WHERE EXISTS(SELECT * FROM #table2 t2 WHERE t2.ID = t1.ID)

But I guess for my Select statement, I'll have to take a different approach.

Using the same sort of subquery above as an existence check is different than the derived table method where values are coming from the derived table and from outer tables. If you perform a select using an existence check as you did above (rather than an update), that would work too.

eg



Select *

FROM dbo.table1 t1

WHERE EXISTS(SELECT * FROM #table2 t2 WHERE t2.ID = t1.ID)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

WayneS SSC Guru Points: 95405 More actions · Answer 2

Mister Ken, I think the problem with the original query you posted is that you built a derived table, and properly aliased it, but then when you joined it to the other table your join condition referenced #table2 instead of the "lookup" alias.

Other than that, I don't see anything wrong with the original query you posted. That is the proper way to build a correlated sub-query.

If you are still having problems, can you post a few sample records for each table, so that we can use that to test with?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

The Subquery will also throw an error

The multi-part identifier "#table1.field2" could not be bound.

On the line:



select field1, count(*) line_count from #table2 where field2 > #table1.field2 group by field1

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281324 More actions · Answer 4

I have played with this some more to fit into the desired results and requirements.



select t.field1, (

select count(*) line_count from #table2 where field2 > t.field2 group by field1

)

from #table1 t

You'll notice that the inner is referencing the outer now. The difference is that the subquery is moved up into the select list rather than remain in the from clause.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mister Ken SSChasing Mays Points: 659 More actions · Answer 5

Jason you are king dude! Just awesome!

I've seen that "(select blah bah)" within the field list before, but never really used it. Now I know the power of it.

I should know that anybody that follows Kimberly Tripp has some skill! 🙂

Thanks again!

Thanks also to all the other fellas that chimed in too. If I had more time, I'd be hanging out here too. Lots of good stuff.

SQLRNNR SSC Guru Points: 281324 More actions · Answer 6

Mister Ken (4/14/2010)

Jason you are king dude! Just awesome!

I've seen that "(select blah bah)" within the field list before, but never really used it. Now I know the power of it.

I should know that anybody that follows Kimberly Tripp has some skill! 🙂

Thanks again!

Thanks also to all the other fellas that chimed in too. If I had more time, I'd be hanging out here too. Lots of good stuff.

You're welcome. And thanks for the compliments.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events