CODEWITHSUNDEEP
sql-serversql-update
user3115933
user3115933

Reputation: 4443

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

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?

Upvotes: 0

Views: 39

Answers (2)

Appelman
Appelman

Reputation: 126

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

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

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 ...

Upvotes: 1

Related Questions