CODEWITHSUNDEEP
sql-server
Questieme
Questieme

Reputation: 993

Changing a column's values depending on another table's values in SQL Server

I have a column called Status in table dbo.Dependencies and columns ReleaseId, TaskId and PhaseId in table dbo.DependencyList. I have been trying to put together a code that will do the following: if the three columns that I enumerated from the second table are NULL, then Status should be 0 in the first table. If any of the columns is not null, then Status should become 1.

DependencyList also has a Foreign Key to Dependencies called DependencyId.

SELECT TaskId
    ,ReleaseId
    ,PhaseId
    ,CASE 
        WHEN (
                TaskId
                AND ReleaseId
                AND PhaseId
                ) IS NULL
            THEN UPDATE Dependencies SET STATUS = '0' WHERE Id = '3004'
        ELSE UPDATE Dependencies SET STATUS = '1' WHERE Id = '3004'
        END
FROM Dependencies
INNER JOIN DependencyList ON Dependencies.Id = DependencyList.DependencyId
WHERE Dependencies.Id = '3004'

That's the code that I came up with, with no success. The errors are as it follows:

Msg 4145, Level 15, State 1, Line 12
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'ELSE'.

Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'FROM'.

What is the correct way in which this should be done?

Upvotes: 0

Views: 131

Answers (3)

user212514
user212514

Reputation: 3130

You can use the CASE while joining like this:

UPDATE dependencies
SET STATUS = CASE WHEN TaskId IS NULL AND ReleaseId IS NULL AND PhaseId IS NULL 
                  THEN '0' 
                  ELSE '1' 
             END
FROM Dependencies 
INNER JOIN DependencyList ON Dependencies.Id=DependencyList.DependencyId WHERE Dependencies.Id='3004'

Upvotes: 2

Dan Guzman
Dan Guzman

Reputation: 46213

CASE in SQL Server is an expression, not a control flow statement. You could use a CASE as an expression to update Status like the example below.

UPDATE dbo.Dependencies
SET Status =
    CASE WHEN TaskId IS NULL AND ReleaseId IS NULL AND PhaseId IS NULL THEN '0' ELSE '1' END
WHERE Id='3004'
 AND Dependencies.Id IN(
SELECT DependencyList.DependencyId
FROM dbo.DependencyList
);

Upvotes: 1

Max Zolotenko
Max Zolotenko

Reputation: 1132

Try this:

UPDATE  T1
SET     T1.STATUS = CASE WHEN COALESCE(T2.TaskId, T2.ReleaseId, T2.PhaseId) IS NULL THEN 0 ELSE 1 END
FROM    Dependencies T1
INNER JOIN DependencyList T2 ON T1.Id=T2.DependencyId
WHERE   T1.Id='3004'

Upvotes: 1

Related Questions