locked
Update Field based on value of another field RRS feed

  • Question

  • User1210767569 posted

    I want to find all lines of data that have a value, say 'N/A'

    I want to then update ALL the lines of data where they have the same CommonID into the NewDataHere Column so in the example below ID 1 and 2 would be affected

    DECLARE @sqlPRatingChange NVARCHAR(MAX)
    SET @sqlPRatingChange = 'UPDATE TblAsbestos SET  CurrentSiteRiskLevel = ''NA''  
    WHERE (SELECT UPRN FROM TBLASBESTOS WHERE OverallRiskCategory = ''N/A'')'''
    EXEC (@sqlPRatingChange) 

    Code for far:

    ID-VALUE-NewDataHere

    1-N/A-N/A

    2-C-N/A

    2-N/A-N/A

    1-A-N/A

    3-B-''

    Wednesday, December 16, 2015 12:39 PM

Answers

  • User452040443 posted

    Try something like this:

    with CTE_Count as
    (
        select ID
        from TblAsbestos
        group by ID
        having 
            COUNT(1) > 1 AND
            COUNT(DISTINCT OverallRiskCategory) = 1
    )
    
    update t
    set t.CurrentSiteRiskLevel = t.OverallRiskCategory
    from TblAsbestos as t
    where
        t.CurrentSiteRiskLevel <> t.OverallRiskCategory and
        exists(select 1 from CTE_Count as c where c.ID = t.ID)

    Hope this helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, December 16, 2015 1:33 PM