How to Update if all value matches in another table ?
I have 2 Tables BLL & BML .
I want to update Table (BLL)'s status(Close) if related IDs in Table(BML) have All status Values (Close).
Data Structure as below
_______________________________
Table Name : BLL
==========================
Batch_Id Status
--------------------------------
1 Pending
2 Pending
3 Pending (Should be "Close" after query run)
4 Pending (Should be "Close" after query run)
Table Name : BML
=====================
Batch_Id Status
-----------------------
1 Pending
1 Close
2 Pending
2 Close
3 Close
3 Close
4 Close
4 Close
-----------------------
Note : Here BML table can have multiple Values for Each ID
Also logic of flow is that we can not pass value of ID to compare. so all records of tables must be considered & related data must be updated.
how to make Query ?
Is loop will be necessary ? Can we avoid it ? How ?
How to make query for this in SQL 2005 ?
Thanks in advance.
Regards,
Yogesh