Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2016
    Posts
    1

    Unanswered: using case statement in update clause

    Hi,
    I need to do something like this shown below in Sybase.

    UPDATE Services
    SET a.Description=CASE b.Mnemonic WHEN 'DES' THEN b.ItemValue END
    FROM Services a, ComponentSpecValue b
    WHERE a.DetailId=b.DetailId

    i.e to update 'description' field in table 'Services', when the field 'Mnemonic' contains 'DES' in table ComponentSpecValue. Both these tables connected with DetailId.

    The above query getting executed. But, not updating Description field at all.

    Thanks in advance.

    Makesh

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    You can't use CASE ,but you can use SWITCH statement.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Works fine for me e.g.
    Code:
    CREATE TABLE #t1 (colid INT, descr VARCHAR(30) NULL)
    
    INSERT INTO #t1 
    SELECT colid,'garbage' 
    FROM syscolumns 
    WHERE id=object_id('sysobjects')
      AND type IN (39,61)
    
    SELECT * FROM #t1 -- before
    
    UPDATE #t1
    SET descr=CASE WHEN c1.type=39 THEN c1.name END 
    FROM #t1 t1
    JOIN syscolumns c1
    ON c1.colid=t1.colid
    AND c1.id=object_id('sysobjects')
    
    SELECT * FROM #t1 -- after
    
    DROP TABLE #t1
    
    (4 rows affected)
     colid       descr                          
     ----------- ------------------------------ 
               1 garbage                        
              10 garbage                        
              11 garbage                        
              21 garbage                        
    
    (4 rows affected)
    (4 rows affected)
     colid       descr                          
     ----------- ------------------------------ 
               1 name                           
              10 NULL                           
              11 NULL                           
              21 loginame                       
    
    (4 rows affected)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •