Conditional Update statement

Hello, I need to do a conditional update on the following table:

CREATE TABLE [#TimesheetReport](
      [DateWorked] [varchar](50),
      [JobNumber] [varchar](50),
      [HoursWorked] [decimal](8, 2),
      [MileageTotal] [decimal](8, 2),
      [MileageDescription] [varchar](250),
      [MileagePaid] [decimal](8, 2),
      [HotelAmountPaid] [decimal](8, 2),
      [MealsAmountPaid] [decimal](8, 2),
      [OtherAmountPaid] [decimal](8, 2),
      [ClientId] int
)

if clientid = 1

update #TimesheetReport
Set MileagePaid =  (MileageTotal * 1.00)

else

update #TimesheetReport
Set MileagePaid =  (MileageTotal * .51)

How do you do this type of update???
gogetsomeAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Use a CASE block to test for the ClientID value.

update #TimesheetReport
Set MileagePaid =  CASE WHEN ClientID = 1 THEN  MileageTotal * 1.00 ELSE MileageTotal * .51 END
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<potentially stupid comment>
In the question the table is created, and the next step is the update, but a newly-created table will have no rows in it, so there's nothing to update.

Not sure if the CREATE TABLE is there just to frame up the question, or if this is your total script.
0
 
gogetsomeAuthor Commented:
Exactly what I needed! Thanks,
That table was just an example. I fill it first then perform the update. ;-)
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thought so.  Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.