Sintax error in update table

All Replies

• Monday, February 04, 2013 5:00 PM

   

   
  

  0

  Hello,
  
  Revised sytax below

  update dimcustomer
  set fullname=FirstName+case WHEN isnull(middlename,'')  = ''
  Then ''
  else middlename  
  END + LastName

  • Reply
  • Quote

   
• Monday, February 04, 2013 5:04 PM

   

   
  

  0

  Try:

  update dimcustomer set fullname=FirstName+case WHEN middlename IS NULL then '' else middlename END + LastName

  
  

  You could also shorten it by removing the case statement in this case with ISNULL.

  update dimcustomer set fullname=FirstName+ isnull(middlename,'') + LastName

  
  

  I'm not sure if you meant to leave spaces out of fullname or not so I have also omitted them.

  
  

  • Edited by Barry Marshall Monday, February 04, 2013 5:05 PM
  • Marked As Answer by DIEGOCTN Monday, February 04, 2013 5:10 PM
  •  
  • Reply
  • Quote

   
• Monday, February 04, 2013 5:04 PM

   

   
  

  0

  Try something like below:

  update dimcustomer set fullname=FirstName+case WHEN IsNull(middlename,'') = ''  then '' else middlename END

  ---

  Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

  • Reply
  • Quote

   
• Monday, February 04, 2013 5:09 PM

   

   
  

  0

  ISNULL() is TSQL, so you might look at using COALESCE which is ANSI SQL.

  UPDATE dimcustomer
  SET FullName = FirstName + COALSECE(' ' + MiddleName, '') + ' ' + LastName

  Other programming notes:

  I would reverse the naming convention to become NameFull, NameFirst, etc so that all the Name fields are ordered if you use some tool that examines the table structure and reports the field names in alphabetical order

  Verify you have spaces between the names.

  ISNULL http://msdn.microsoft.com/en-us/library/ms184325.aspx

  COALESCE http://msdn.microsoft.com/en-us/library/ms190349.aspx

  CASE WHEN http://msdn.microsoft.com/en-us/library/ms181765.aspx

  • Reply
  • Quote

   
• Monday, February 04, 2013 5:11 PM

   

   
  

  0

  Try -

  UPDATE dimcustomer 
  SET fullname=FirstName+' '+ISNULL(NULLIF(middlename,NULL),'')+' '+LastName

  
  

  ---

  Narsimha

  • Reply
  • Quote