Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
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 <tab> 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 ?
Thanks in advance.

Regards,
Yogesh
Posted

I think this will do it:

SQL
UPDATE
  BLL
SET
  BLL.Status = 'Close'
FROM
  BLL
    INNER JOIN
  (SELECT BML.Batch_Id,
    FROM BML
    INNER JOIN BLL ON BML.Batch_Id = BLL.Batch_Id
    GROUP BY BML.Batch_Id
    HAVING Count(Distinct BML.Status) = 1 AND MIN(BML.Status) = 'Close') CloseList
  ON
    BLL.Batch_Id = CloseList.Batch_Id

So the nested query creates a list of the valid close states which is joined to the target table to restrict the rows which are updated.

The query creates a join from the source table to target table and groups on batch id selecting just rows where there is one 1 distinct status and the value of the status is 'Close'.

SQL
SELECT BML.Batch_Id,
    FROM BML
    INNER JOIN BLL ON BML.Batch_Id = BLL.Batch_Id
    GROUP BY BML.Batch_Id
    HAVING Count(Distinct BML.Status) = 1 AND MIN(BML.Status) = 'Close'
 
Share this answer
 
v4
Comments
VJ Reddy 9-Jun-12 20:59pm    
Good answer. 5!
Manas Bhardwaj 9-Jun-12 21:42pm    
correct +5
reddy2010 3-Jun-16 19:16pm    
I would add Where Clause at the end , so that we dont end up updating the same record .

Where BLL.status<>'Close'
The Solution 1 given by Stephen Hewison 2 is good.

Alternatively, the following query can be used to Update Status in BLL when the Status is close for all corresponding Batch_Ids in BML table
SQL
UPDATE
  BLL
SET
  BLL.Status = 'Close'
WHERE
  BLL.Batch_Id 
  IN 
    (
        --List all Batch_Id which have all status as Close
	SELECT BML.Batch_Id
	FROM BML
	GROUP BY BML.Batch_Id
	HAVING SUM(CASE WHEN bml.Status <> 'Close' THEN 1 ELSE 0 END)  = 0
    )

The sub query in the WHERE clause list all Batch_Ids which have all Status field as Close in BML table which ensures that only those Batch_Id status in BLL table is updated with Close.
 
Share this answer
 
Comments
Manas Bhardwaj 9-Jun-12 21:42pm    
correct +5
VJ Reddy 9-Jun-12 23:11pm    
Thank you, Manas :)
Stephen Hewison 10-Jun-12 4:15am    
Yes good answer. The having clause is a bit tidier than mine. +5
VJ Reddy 10-Jun-12 5:01am    
Thank you, Stephen :)
member60 11-Jun-12 0:05am    
my 5!
Dear Friend,

Hope this sample query will be helping you.

SQL
update BLL set status='CLOSE' from
--select * from
BLL a (nolock), BML b (nolock) where a.ID=b.ID



Regards,
AP
 
Share this answer
 
Comments
VJ Reddy 10-Jun-12 5:24am    
This will not work to update as per the requirement stated in the question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900