How to update column based on select statement result-CodePudding

I have a table with following columns :

SalaryStructure:

Id   StructureName   IsApplicable  IsActive

IsApplicable - bit, not null

"IsApplicable" is a newly added column which has all the values as "false" by default.

Now, I want to update "IsApplicable" based on pattern matching logic and then set the value of "IsApplicable" based on that.

Query:

SELECT 
        CASE 
        WHEN (StructureName LIKE '%Associate1%' OR StructureName Like '%Tier1%') 
        THEN 'No' ELSE 'Yes' END AS IsApplicable,
    FROM SalaryStructure
        WHERE IsActive = 0

I want to run above script on same table SalaryStructure and update the values "Yes" and "No" in the column "IsApplicable".

But I am not getting how to include this part in "Update" statement. I want update all the records in the SalaryStructure table.

Can someone please help me?

CodePudding user response：

Neither 'Yes' or 'No' are valid bit values, a non-NULLable bit column can store 1 or 0 and that is it. I therefore assume 'No' should be 0 and 'Yes' should be 1.

As for the UPDATE it would, in truth, look like another other UPDATE on a single table. UPDATE...SET...WHERE:

UPDATE dbo.SalaryStructure
SET IsApplicable = CASE WHEN (StructureName LIKE '%Associate1%' OR StructureName LIKE '%Tier1%') THEN 0
                        ELSE 1
                   END
WHERE IsActive = 0;