Combine script into one update statement 1

[snufse1]

Is the a way to combine below into one update?

update #Equential_LaborHrsDetailTable
set eq_equipment_name = ' ', eq_equipment_id = ' '
where eq_equipment_id is null

update #Equential_LaborHrsDetailTable
set eq_employee_hours = 0
where eq_employee_hours is Null

update #Equential_LaborHrsDetailTable
set eq_equipment_hours = 0
where eq_equipment_hours is Null

 

[PWise]

update #Equential_LaborHrsDetailTable
set eq_equipment_name = case when eq_equipment_id is null
then ' ' else eq_equipment_name end ,

eq_equipment_id = case when eq_equipment_id is null
then ' ' else eq_equipment_id end ,
eq_employee_hours = case when eq_employee_hours is null
then 0 else eq_employee_hours end ,
eq_equipment_hours=case when eq_equipment_hours is null
then 0 else eq_equipment_hours end

 

[bborissov]

[COLOR=blue]update[/color] #Equential_LaborHrsDetailTable
       [COLOR=blue]set[/color] eq_equipment_name  = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_equipment_id [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] [COLOR=red]' '[/color]
                                     [COLOR=blue]ELSE[/color] eq_equipment_name [COLOR=blue]END[/color],
           eq_equipment_id    = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_equipment_id [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] [COLOR=red]' '[/color]
                                     [COLOR=blue]ELSE[/color] eq_equipment_id [COLOR=blue]END[/color],
           eq_employee_hours  = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_employee_hours  [COLOR=blue]IS[/color] NULL 
                                          [COLOR=blue]THEN[/color] 0
                                     [COLOR=blue]ELSE[/color] eq_employee_hours [COLOR=blue]END[/color],
           eq_equipment_hours = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_equipment_hours [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] 0
                                     [COLOR=blue]ELSE[/color] eq_equipment_hours [COLOR=blue]END[/color]

But if you have huge number of records in that temp table your approach with 3 different updates will be faster, just becuase you filter only these records you need. With that "all in one" update ALL records will be checked one by one.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

And because I just hate a query w/o WHERE

[COLOR=blue]update[/color] #Equential_LaborHrsDetailTable
       [COLOR=blue]set[/color] eq_equipment_name  = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_equipment_id [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] [COLOR=red]' '[/color]
                                     [COLOR=blue]ELSE[/color] eq_equipment_name [COLOR=blue]END[/color],
           eq_equipment_id    = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_equipment_id [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] [COLOR=red]' '[/color]
                                     [COLOR=blue]ELSE[/color] eq_equipment_id [COLOR=blue]END[/color],
           eq_employee_hours  = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_employee_hours  [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] 0
                                     [COLOR=blue]ELSE[/color] eq_employee_hours [COLOR=blue]END[/color],
           eq_equipment_hours = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] eq_equipment_hours [COLOR=blue]IS[/color] NULL
                                          [COLOR=blue]THEN[/color] 0
                                     [COLOR=blue]ELSE[/color] eq_equipment_hours [COLOR=blue]END[/color]
[COLOR=blue]WHERE[/color] eq_equipment_id    [COLOR=blue]IS[/color] NULL OR
      eq_employee_hours  [COLOR=blue]IS[/color] NULL OR
      eq_equipment_hours [COLOR=blue]IS[/color] NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.