More efficient code to Set data values 1

[M8tt]

I have the following code to set the field MATERIAL_DESC on the basis of a value in the MATERIAL field.

Update [DB1].[dbo].[DIM_MATERIAL]
Set MATERIAL_DESC = 'Consumables'
From DIM_MATERIAL
Where MATERIAL = 'Z0001'

Update [DB1].[dbo].[DIM_MATERIAL]
Set MATERIAL_DESC = 'Repairables'
From DIM_MATERIAL
Where MATERIAL = 'Z0002'

This works okay however what I am wondering is if there is a way to do this from a single statement rather than by running both sets of code?

The type of thing I was hoping to be able to do was something like.... If MATERIAL = 'Z0002' then Set MATERIAL_DESC = 'Repairables' Else If MATERIAL = 'Z0001' etc etc .... but I cannot work out how to do this or something similar.

Can anyone assist?

Many Thanks in advance.

 

[donutman]

You can use the Case statement for that:

[Blue]UPDATE[/Blue] [DB1].[dbo].[DIM_MATERIAL]
   [Blue]SET[/Blue] MATERIAL_DESC [Gray]=[/Gray] [Blue]CASE[/Blue] MATERIAL 
         [Blue]WHEN[/Blue] [red]'Z0001'[/red] [Blue]THEN[/Blue] [red]'Consumables'[/red]
         [Blue]WHEN[/Blue] [red]'Z0002'[/red] [Blue]THEN[/Blue] [red]'Repairables'[/red] [Blue]END[/Blue]
[Blue]WHERE[/Blue] MATERIAL [Blue]IN[/Blue] [Gray]([/Gray][red]'Z0001'[/red][Gray],[/Gray] [red]'Z0002'[/red][Gray])[/Gray]

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]