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
Loading
brunda kPosted Jun 12, 2012, 3:09 AM
SET bl.Status = 'Pending'
from BLL bl inner join BML bm ON bl.Id = bm.ID
and bl.Id not in ( SELECT distinct Id from BML where status <> 'Close')
and bl.status <> 'close'