Update multiple columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update multiple columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-16 : 11:56:57

Ashish writes "How do I update multiple columns in MS SQL Server 2000 ?

I am trying to run following query but it gives me errors.

update aim_temp_sales_rep
 set (salesrep_name, salesrep_group_code, salesrep_group_desc, alpha_sort_level) = 
  (select distinct
     case
       when sa.salesrep_name is null
       then 'Unknown Sales Rep. SR-' + aim_temp_sales_rep.salesrep_num
       else  sa.salesrep_name + ' (LC ' + sa.local_channel + ')'
     end,
          'Non',
          'Non-Global Reps.',
    case
       when sa.salesrep_name is null
       then 'Non-GLBL Sales Rep. - U'
       else  'Non-GLBL Sales Rep. - '+substring(ltrim(sa.salesrep_name),1,1)
    end
    from aim_temp_sales_rep right outer join salesrep_amer sa
    on aim_temp_sales_rep.salesrep_num = sa.salesrep_num
      and sa.business_unit_id = 11)
where aim_temp_sales_rep.salesrep_name is null 
  and aim_sales_rep.salesrep_num != 'UNK'

Errors:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.
Server: Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'where'.

aim_temp_sales_rep table has following columns:
(salesrep_name, salesrep_group_code, salesrep_group_desc, alpha_sort_level) "

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-16 : 12:28:17


update aim_temp_sales_rep
set
  salesrep_name = case
    when sa.salesrep_name is null
    then 'Unknown Sales Rep. SR-' + aim_temp_sales_rep.salesrep_num
    else sa.salesrep_name + ' (LC ' + sa.local_channel + ')'
  end,
  salesrep_group_code = 'Non',
  salesrep_group_desc = 'Non-Global Reps.',
  alpha_sort_level =  case
    when sa.salesrep_name is null
    then 'Non-GLBL Sales Rep. - U'
    else  'Non-GLBL Sales Rep. - '+substring(ltrim(sa.salesrep_name),1,1)
  end
from aim_temp_sales_rep right outer join salesrep_amer sa
  on aim_temp_sales_rep.salesrep_num = sa.salesrep_num
     and sa.business_unit_id = 11
where aim_temp_sales_rep.salesrep_name is null 
  and aim_sales_rep.salesrep_num != 'UNK'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources