Yogesh Patel

Yogesh Patel

  • NA
  • 1
  • 1.1k

How to Update if all value matches in another table ?

Jun 9 2012 2:01 PM
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






Answers (1)