Correlated Sub Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Correlated Sub Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-29 : 08:46:18

Balaji writes "I am trying to delete rows from Table A where key rows do not exist in Table B

select count(*) -- 69501,67141,2360
from Product p
where exists (select material_id
from sales_fact_table s
where s.material_id = p.material_id
)

delete
from Product p
where exists (select material_id
from sales_fact_table s
where s.material_id = p.material_id
)

Why does this statement not work? Is it because ANSI92 SQL not allow it?

Should I use Transact SQl cursors?"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-29 : 08:57:45

thought you wanted the ones that don't exist
delete Product
from Product p
left outer join sales_fact_table s
on s.material_id = p.material_id
where s.material_id is null

your query should probably be
delete Product
from Product p
where not exists (select *
from sales_fact_table s
where s.material_id = p.material_id
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ashok
Yak Posting Veteran

57 Posts

Posted - 2002-10-29 : 09:01:20

EXISTS simply tests whether the subquery returned any rows or not.
even if the sales_fact_table has a single row, the product table count will count all the rows in the product table.
What you need is a join condition or a subquery with a IN()

you should change this :


select count(*) -- 69501,67141,2360
from Product p
where exists (select material_id
from sales_fact_table s
where s.material_id = p.material_id
)

to something like


select count(p.*) -- 69501,67141,2360
from Product as p inner join sales_fact_table as s on 
p.product_id = s. material_id

-ashok
http://www.unganisha.org

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-29 : 09:10:27

quote:

EXISTS simply tests whether the subquery returned any rows or not.
even if the sales_fact_table has a single row, the product table count will count all the rows in the product table.
What you need is a join condition or a subquery with a IN()

That's why is correlated ... your delete with the join will do exactly the opposite of what the poster is requesting ... nr was spot on.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources