How to modify my T-SQL UPDATE query by adding a CONDITIONAL clause?

Question

I'm using SQL Server 2014 and I have an UPDATE query that runs on a table called "ResStayDate" (extract shown below):

ID      StayDate        RateAmount
-----------------------------------
256     2015-11-28      248.00
256     2015-11-29      248.00
256     2015-11-30      248.00
256     2015-12-01        0.00
256     2015-12-02        0.00
256     2015-12-03        0.00

I need to update the RateAmount column and my UPDATE query is as follows:

USE MyDatabase

UPDATE ResStayDate
SET RateAmount = CASE ResID
                    WHEN 256 THEN 155.00
                    ELSE RateAmount
                 END

My problem is that if I run this query "as is", it will update ALL the rows with 155.00. I need to update only those rows where the StayDate is in December 2015.

Output should be like this:

     ID      StayDate      RateAmount
    ---------------------------------
    256     2015-11-28      248.00
    256     2015-11-29      248.00
    256     2015-11-30      248.00
    256     2015-12-01      155.00
    256     2015-12-02      155.00
    256     2015-12-03      155.00

How do I modify my query so that it updates only that part of the ResStayDate table?

Appelman · Accepted Answer · 2015-07-23 10:37:15Z

1

If you specifically only want to affect the records in December, use the DATEPART function:

USE MyDatabase

UPDATE ResStayDate

SET RateAmount = CASE WHEN ResID = 256 and DATEPART(MONTH,StayDate) = 12 THEN 155.00

ELSE RateAmount

END

answered Jul 23, 2015 at 10:37

Appelman

1267 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3115933 Over a year ago

Your solution works fine. However, I have a small question: how do I handle a situation where I need to update a list of ResID? Say, I need to update three ResID (256,289,292) at a go, with each ResID having a different DATEPART? Do I need to re-write the whole code 3 times?

Appelman Over a year ago

You will have to re-write the code 3 times: UPDATE ResStayDate SET RateAmount = CASE WHEN ResID = 256 and DATEPART(MONTH,StayDate) = 12 THEN 155.00 WHEN ResID = 289 and DATEPART(MONTH,StayDate) = 7 THEN 155.00 WHEN ResID = 292 and DATEPART(MONTH,StayDate) = 1 THEN 155.00 ELSE RateAmount END a bit like a switch statement

Carsten Massmann · Accepted Answer · 2015-07-23 10:35:38Z

1

UPDATE ResStayDate
SET RateAmount = 155
WHERE ID=256 and StayDate between '2015-12-1' and '2015-12-31'

This restricts your update to those records where the above mentioned conditions apply. SQL is fairly easy to read that way - not much explanation needed ;-).

Edit

I read your time-restriction only after I posted my first version ...

edited Jul 23, 2015 at 10:35

answered Jul 23, 2015 at 10:32

Carsten Massmann

28.4k3 gold badges25 silver badges49 bronze badges

1 Comment

Raj Over a year ago

How does this address the condition "StayDate is in December 2015."

Collectives™ on Stack Overflow

How to modify my T-SQL UPDATE query by adding a CONDITIONAL clause?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related