CODEWITHSUNDEEP
sqlt-sql
user7
user7

Reputation: 133

update table with multiple conditions

I have table as follows:

SYS_ID  SUB_NET_ID  NODE_NAME   NODE_ID NODE_EQ_NO  NODE_VAR_NO TEMP_ID EQUIP_TYPE  EQ_ID   VAR_ID  VAR_OBJECT  VAR_NAME    VAR_SUBSET  VAR_SET CALC_VAR_TYPE   DATA_TYPE   DOF
15  1   BLEND   1   13  21  16      5   0   BLEND   DEMAND  SELF    BLEND_OUT   VAR CONTINOUS                  
15  1   BLEND   1   14  6   16      6   0   BLEND   DEMAND  BLEND   BLEND   VAR CONTINOUS                  
15  1   DEST    2   5   2   4       7   0   DEST    DEMAND  SELF    DEST_IN VAR CONTINOUS                  
15  1   DEST    2   1   3   4       1   0   DEST    DEMAND  UNDEF   DEST_IN VAR CONTINOUS                  
15  1   DEST    2   4   6   4       4   0   DEST    MFLOW   SELF    DEST_IN VAR CONTINOUS                  
15  1   SALK    5   6   5   13      4   0   SALK    MFLOW   SELF    SALK_OUT    VAR binary                 
15  1   SPEN    7   8   4   13      6   0   SPEN    MFLOW   SELF    SPEN_OUT    VAR integer

I want to update the column data_type to 1 where data_type is continous and update to 0 where it is binary and so on... any suggestion

Upvotes: 0

Views: 355

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

Use the CASE statement for that:

UPDATE my_tbl SET data_type =
 CASE data_type
   WHEN 'continous' THEN '0'
   WHEN 'binary'    THEN '1'
   -- more options
   ELSE data_type  -- to retain original string if no substitute is listed
 END;

You are aware that the data type will still not be a number, but what ever string type it was before, right?

Upvotes: 3

Related Questions