2

I have 3 tables such as

IndentHeader:

 IndentID StatusID -------- ------ 1 5 2 5

IndentDetail: 

IndentID ItemID ItemStatusID -------- ------ ------------ 1 22 4 1 23 4 2 11 4 2 12 3 2 13 3

POIndent:

 POID IndentID ItemID ItemStatusID -------- ------ ------ ------------ 1 1 22 4 1 1 23 4 1 2 11 4

I want to Update IndentHeader table StatusID = 4 when all the Items in the IndentDetail table (based on the IndentID) ItemstatusID becomes 4 otherwise I want to Update IndentHeader StatusID = 3. In the Condition I need to give POID. Based on the POID, the corresponding Indent is considered for both the IndentHeader and IndentDetail table. My desired Result should be like this:

IndentHeader:

 IndentID StatusID -------- ------ 1 4 2 3

How to achieve this? Please help me.

Hi all, this is my update command. But it update both the StatusID in IndentHeader as 4.

 UPDATE STR_IndentHeader SET StatusID = IID FROM (SELECT STR_IndentDetail.IndentID, MIN(ItemStatusID) AS 'IID' FROM STR_IndentDetail INNER JOIN PUR_POIndent PP ON PP.IndentID = STR_IndentDetail.IndentID AND PP.ItemID = STR_IndentDetail.ItemID WHERE ItemStatusID = 4 AND PP.POID = 1 GROUP BY STR_IndentDetail.IndentID) ID WHERE ID.IndentID = STR_IndentHeader.IndentID

I need all your valuable contributions. please help me...

Improve this question
4
  • Do you know the UPDATE command at all? If so, you should at least attempt this and show us your attempt. Commented Sep 5, 2011 at 5:54
  • @Andrew: I posted my update query. please have a look at it. Commented Sep 5, 2011 at 5:59
  • What do you actually mean when you say "Here I want to pass only the POID, So the remainings should happen above as following the table relationships" ? It does not make sense. Commented Sep 5, 2011 at 6:14
  • @Hugh: In the Condition I need to give POID. So based on the corresponding IndentID to the POID in the POIndent table, Update Query should get executed. Commented Sep 5, 2011 at 6:34

2 Answers 2

Reset to default
3

My [revised] solution use one ALL subquery to check ItemStatusID condition:

DECLARE @MyPOID INT = 1; DECLARE @IndentHeader TABLE ( IndentID INT PRIMARY KEY ,StatusID INT NOT NULL ); INSERT @IndentHeader VALUES (1,5); INSERT @IndentHeader VALUES (2,5); INSERT @IndentHeader VALUES (3,5); DECLARE @IndentDetail TABLE ( IndentID INT NOT NULL ,ItemID INT NOT NULL ,ItemStatusID INT NOT NULL ,PRIMARY KEY(IndentID, ItemID) ); INSERT @IndentDetail VALUES (1,22,4); INSERT @IndentDetail VALUES (1,23,4); INSERT @IndentDetail VALUES (2,11,4); INSERT @IndentDetail VALUES (2,12,3); INSERT @IndentDetail VALUES (2,13,3); INSERT @IndentDetail VALUES (3,22,3); DECLARE @POIndent TABLE ( POID INT ,IndentID INT NOT NULL ,ItemID INT NOT NULL ,ItemStatusID INT NOT NULL ); INSERT @POIndent VALUES (1,1,22,4); INSERT @POIndent VALUES (1,1,23,4); INSERT @POIndent VALUES (1,2,11,4); INSERT @POIndent VALUES (2,3,22,4); SELECT * FROM @IndentHeader h; SELECT * FROM @IndentDetail d; SELECT * FROM @POIndent po; UPDATE @IndentHeader SET StatusID = CASE WHEN 4 = ALL(SELECT d.ItemStatusID FROM @IndentDetail d WHERE d.IndentID = h.IndentID) THEN 4 ELSE 3 END FROM @IndentHeader h WHERE h.IndentID IN (SELECT po.IndentID FROM @POIndent po WHERE po.POID = @MyPOID); SELECT * FROM @IndentHeader h;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Yes. But it updates all the IndentID's StatusID. But I want to Update only the IndentID exists for the Corresponding POID in the POINdent table.
@thevan: This is a valuable starting point. It would be polite and correct to upvote event if this is not the final solution.
@Sahleen: thank you sahleen. this statement gives the correct answer for my problem. thank you...
@sahleen - I've never used (or knew) ALL. +1 for teaching me a new trick.
2

The gist of it is to

  • find the minimum ItemStatusID for each IndentID
  • Join this back with IndentHeader
  • Use these in an UPDATE FROM statement

SQL Statement

UPDATE IndentHeader SET StatusID = ihd.ItemStatusID FROM IndentHeader ih INNER JOIN ( SELECT ItemStatusID = MIN(id.ItemStatusID) , ih.IndentID FROM IndentHeader ih INNER JOIN IndentDetail id ON id.IndentID = ih.IndentID INNER JOIN POIndent pi ON pi.IndentID = id.IndentID WHERE pi.POID = 1 GROUP BY ih.IndentID ) ihd ON ihd.IndentID = ih.IndentID

Test script

;WITH IndentHeader (IndentID, StatusID) AS ( SELECT 1, 5 UNION ALL SELECT 2, 5 ) , IndentDetail (IndentID, ItemID, ItemStatusID) AS ( SELECT 1, 22, 4 UNION ALL SELECT 1, 23, 4 UNION ALL SELECT 2, 11, 4 UNION ALL SELECT 2, 12, 3 UNION ALL SELECT 2, 13, 3 ) , POIndent (POID, IndentID, ItemID, ItemStatusID) AS ( SELECT 1, 1, 22, 4 UNION ALL SELECT 1, 1, 23, 4 UNION ALL SELECT 1, 2, 11, 4 ) --UPDATE IndentHeader --SET StatusID = ihd.ItemStatusID SELECT ih.IndentID, ihd.ItemStatusID FROM IndentHeader ih INNER JOIN ( SELECT ItemStatusID = MIN(id.ItemStatusID) , ih.IndentID FROM IndentHeader ih INNER JOIN IndentDetail id ON id.IndentID = ih.IndentID INNER JOIN POIndent pi ON pi.IndentID = id.IndentID WHERE pi.POID = 1 GROUP BY ih.IndentID ) ihd ON ihd.IndentID = ih.IndentID
Improve this answer

6 Comments

mmm... you have edited your question to include your current query. At first sight, it looks identical to what I propose but AFAIK, the query is valid. I'll have to work out some testscript
@thevan: the testscript outputs ItemStatusID 4 for IndentID 1 and ItemStatusID 3 for IndentID 2. Is this not what you need?
@thevan: the testscript outputs ItemStatusID 4 for IndentID 1 and ItemStatusID 3 for IndentID 2. Is this not what you need?
@thevan: This is a valuable starting point. It would be polite and correct to upvote event if this is not the final solution.
@thevan - don't mention it but I still puzzled by why this did not work for you.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.