Updating data in DB

  • Imma try to be clear and will show a table =) Sorry to everyone for being not clear in the past.

    I have such table: http://img170.imageshack.us/img170/4810/temp12nv.jpg

    I'm trying to accomplish procedure that would insert values into column SApproved. So I have next variables passed into procedure @LeaveID, @RegFee, @ETFee, @MiscFee, @LodgingFee, @FoodFee

    So how can i match it? I mean how can I make RegFee go into a row with Name Registration Fee? And etc?

  • Simple thing like that:

    UPDATE Leave_Professional_Funds SET [SApproved] = @RegFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Registration Fee"

     UPDATE Leave_Professional_Funds SET [SApproved] = @ETFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Mode of Travel"

     UPDATE Leave_Professional_Funds SET [SApproved] = @MiscFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Misc Cost"

     UPDATE Leave_Professional_Funds SET [SApproved] = @LodgingFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Lodging Cost"

     UPDATE Leave_Professional_Funds SET [SApproved] = @FoodFee WHERE [LeaveRequestID] = @LeaveID AND [Name] = "Food Cost"

    Doesn't work for some reason. (Invalid Column name 'Misc Cost', 'Loding Cost' and etc.)

  • got it to work.

  • First:

    You should replace your " for '

    Second:

    all can be replaced using a case statement that way you make a single pass to the table:

    UPDATE Leave_Professional_Funds SET [SApproved] = case  when  [Name] = 'Registration Fee' then  @RegFee

           when  [Name] = 'Mode of Travel'   then  @ETFee 

           when  [Name] = 'Misc Cost'        then  @MiscFee

           when  [Name] = 'Lodging Cost'     then  @LodgingFee

           when  [Name] = 'Food Cost'        then  @FoodFee

           else  [SApproved] end

    WHERE [LeaveRequestID] = @LeaveID

     


    * Noel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply