DATEDIFF combined with UPDATE STATEMENT

Jes Sie

I have a query below:

select DATEDIFF(DAY, DateRequested, GETDATE()) as NumberOfDays from tblSeriesRequest

if the NumberOfDays is greater than 30, then I want to perform an update

I tried the query below but it updates the whole rows.

if exists(select 1 from tblSeriesRequest where (DATEDIFF(DAY, DateRequested, GETDATE())) > 30 and IsApproved <> 1)
begin
UPDATE [dbo].[tblSeriesRequest] SET [IsCancelled] = 1
end

Can anyone show me how to do this? Many thanks in advance

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar8y ago
Accepted answer
Hi,

Please try this one

You are checking exists condition if it matches for any record then you query run but you do not have condition on your update query thats why it is updating all rows.
UPDATE tblSeriesRequestSET IsCancelled= 1 where IsApproved <> 1 and DATEDIFF(day,DateRequested,getdate()) > 30
Jignesh Kumar8y ago
Glad to know it helps to you. Please accept the answer if your issue resolved
Jes Sie8y ago
Thanks @Jignesh Kumar, your query works too.
Jes Sie8y ago
I finally found an answer to my question:
if exists(select 1 from tblSeriesRequest where (DATEDIFF(DAY, DateRequested, GETDATE())) > 30 and IsApproved <> 1)
        begin
            UPDATE [dbo].[tblSeriesRequest] SET [IsCancelled] = 1
            WHERE (SELECT DATEDIFF(DAY, DateRequested, GETDATE())) > 30 AND IsApproved <> 1
        end