Update Statement - Use Case to choose update col

[mwa]

Is it possible to use a case statement to choose the column to update? For instance, in the following example I have 4 columns. I want the parameter @p_name to go into the first non-null column of those 4:

update InterviewSlots
set case when isnull(slot1_reserved_by,'')= '' then
           slot1_reserved_by 
        when isnull(slot2_reserved_by,'')= '' then
           slot2_reserved_by 
        when isnull(slot3_reserved_by,'')= '' then
           slot3_reserved_by 
        when isnull(slot4_reserved_by,'')= '' then
           slot4_reserved_by
    end = @p_name
where time_slot_start = @p_date

This returns the error "Incorrect syntax near the keyword 'case'". Do I have the syntax wrong, or is this just not possible?

Thanks in advance...

mwa
<><

 

[RiverGuy]

You can't do that. I would create parameters for all four, with defaults of NULL (assuming you don't allow your users to update an actual value back to a NULL). You can then use the following logic

update InterviewSlots
SET slot1_reserved_by = ISNULL(@slot1_reserved_by, slot1_reserverd_by),
SET slot2_reserved_by = ISNULL(@slot2_reserved_by, slot2_reserverd_by),
SET slot3_reserved_by = ISNULL(@slot3_reserved_by, slot3_reserverd_by),
SET slot4_reserved_by = ISNULL(@slot4_reserved_by, slot4_reserverd_by)
where time_slot_start = @p_date

 

[bborissov]

You could use one parameter, but the update statement will be ugly. I'll go with RiverGuy's suggestion:

[COLOR=blue]update[/color] InterviewSlots
       [COLOR=blue]set[/color] slot1_reserved_by = [COLOR=#FF00FF]isnull[/color](slot1_reserved_by,@p_name),
           slot2_reserved_by = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] slot1_reserved_by [COLOR=blue]IS[/color] NULL AND
                                         slot2_reserved_by [COLOR=blue]IS[/color] NULL
                                         [COLOR=blue]THEN[/color] slot2_reserved_by
                               [COLOR=blue]ELSE[/color]
                                    slot2_reserved_by [COLOR=blue]END[/color],
           slot3_reserved_by = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] slot1_reserved_by [COLOR=blue]IS[/color] NULL AND
                                         slot2_reserved_by [COLOR=blue]IS[/color] NULL AND
                                         slot3_reserved_by [COLOR=blue]IS[/color] NULL
                                         [COLOR=blue]THEN[/color] @p_name
                               [COLOR=blue]ELSE[/color]
                                    slot3_reserved_by [COLOR=blue]END[/color],
           slot4_reserved_by = [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] slot1_reserved_by [COLOR=blue]IS[/color] NULL AND
                                         slot2_reserved_by [COLOR=blue]IS[/color] NULL AND
                                         slot3_reserved_by [COLOR=blue]IS[/color] NULL AND
                                         slot4_reserved_by [COLOR=blue]IS[/color] NULL
                                         [COLOR=blue]THEN[/color] @p_name
                               [COLOR=blue]ELSE[/color]
                                    slot4_reserved_by [COLOR=blue]END[/color]
[COLOR=blue]where[/color] time_slot_start = @p_date

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.