CODEWITHSUNDEEP
sql-server
Elad Luvka
Elad Luvka

Reputation: 29

Determine column value according to other columns in the same table

I want to determine column value for each row according to other columns in same table.

For example I have this table:

BbName 
DBStatus (online , offline , suspect.. etc) 
IsAutoClose (0 , 1)
IsAutoCreateStatistics (0 , 1)
IsAutoShrink (0 , 1) 
CheckDate (datetime)
Status(0 , 1)

Once in a month I insert data into these columns (DbName, DBStatus, IsAutoClose, IsAutoCreateStatistics, IsAutoShrink, CheckDate)

I want to determine the status column value for each new row according to the other columns in that row.

Something like this 

(IF DBStatus IN (offline, suspect) OR 
 IF IsAutoClose = 1 OR
 IF IsAutoCreateStatistics = 0) THEN 
    set Status = 1 
ELSE 
    set Status = 0

What is best way to that?

After insert trigger ?

Upvotes: 0

Views: 64

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Just add a computed column:

alter table thistable
    add status as (case when DBStatus IN (offline, suspect) OR 
                             IsAutoClose = 1 OR
                             IsAutoCreateStatistics = 0
                        then 1 else 0
                   end) ;

It will be calculated on-the-fly, so it is always up-to-date.

Upvotes: 0

A ツ
A ツ

Reputation: 1267

change status into a calculated column 

alter table  temp 
add status as 
  case when DBStatus IN ('offline', 'suspect') then 1
       when IsAutoClose = 1                    then 1
       when IsAutoCreateStatistics = 0         then 1
       ELSE 0 
  end;

or you create a view on top of the table with a status column with that formula

Upvotes: 0

Posiedon
Posiedon

Reputation: 162

If you want to do in in one update query then try this:

update Yourtable
Set Status = CASE WHEN ( DBStatus IN (offline, suspect) OR 
             IsAutoClose = 1 OR
            IsAutoCreateStatistics = 0) THEN 1
        ELSE 0 
        END
WHERE (...Your condition for which you want data updated...)

Upvotes: 1

marc_s
marc_s

Reputation: 754230

You could just run an UPDATE statement after the insert is done that updates your column Status - something like this:

-- update all rows with Status IS NULL to 1, if one of those 
-- given conditions is met
UPDATE dbo.YourTable
SET Status = 1
WHERE Status IS NULL     -- update those that have no value yet
  AND (DBStatus IN (offline, suspect) 
       OR IsAutoClose = 1 
       OR IsAutoCreateStatistics = 0) 

-- now update all remaining rows with Status IS NULL to 0
UPDATE dbo.YourTable
SET Status = 0
WHERE Status IS NULL     -- update those that have no value yet

Upvotes: 1

Related Questions