How to update the column using two tables based on conditions?

I have two tables such as STR_IndentHeader and STR_IndentDetail.

STR_IndentDetail:

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

STR_IndentHeader:

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

Here I want to Update the STR_IndentHeader StatusID = 4, When all the STR_IndentDetail.ItemID's ItemStatusID = 4 with respect to IndentID. Else I want to Update the STR_IndentHeader.StatusID = 3.

In the above tables, In STR_IndentDetail, For IndentID "1", all the ItemStatusID of the Items is 4. So we have Update STR_IndentHeader.StatusID = 4. But for IndentID "2", One Item's(ie. ItemID=11) ItemStatusID = 4 and the Remaining two items ItemStatusID = 3. So In this case, we have to update STR_IndentHeader.StatusID = 3. I hope it would give better idea. How to do this?

My Desired Result for the above tables would be like this:

STR_IndentHeader:

  IndentID           St开发者_运维问答atusID
  --------          -----------
    1                  4
    2                  3   

Based off the information provided I am assuming that you want the statusID in STR_IndentHeader to be the smallest ItemStatusID value from STR_IndentDetail for that IndentID.

If this is the case please try the below:

update STR_IndentHeader
set statusid = minitemstatusid
from
    (select indentid,MIN(itemstatusid) as minitemstatusid
    from STR_IndentDetail  
    group by indentid) id 
where id.IndentID = STR_IndentHeader.indentid

EDIT:

Based off comments if you want to statically apply an ItemStatusID of 3 if the statusID is not 4 then:

update STR_IndentHeader
set statusid = case minitemstatusid when 4 then 4 else 3 end 
from
    (select indentid,MIN(itemstatusid) as minitemstatusid
    from STR_IndentDetail  
    group by indentid) id 
where id.IndentID = STR_IndentHeader.indentid

Here is one way you can do this using CROSS APPLY available in SQL Server 2005. Hope that helps.

UPDATE      SH
SET         SH.StatusID = (CASE WHEN DC.DistinctCount = 1 THEN 4 ELSE 3 END)
FROM        dbo.STR_IndentHeader    SH
CROSS APPLY (
                SELECT      SD.IndentID
                        ,   COUNT(DISTINCT ItemStatusID)  AS DistinctCount
                FROM        dbo.STR_IndentDetail    SD
                WHERE       SH.IndentID             = SD.IndentID
                GROUP BY    SD.IndentID
            ) DC

This worked for me when I tested it setting up a database with your sample data:

UPDATE STR_IndentHeader ih
SET StatusID = (SELECT MIN(ItemStatusID) FROM STR_IndentDetail id WHERE id.IndentID = ih.IndentID)
WHERE IndentID IN (SELECT DISTINCT IndentID FROM PUR_POIndent WHERE POID = 8)