SQL update set case when else

October 26, 2023

I’m trying to update a value based on some condition. The code I wish to edit to use this condition looks like this (and works without the condition):

UPDATE [SomeSchema].[Synchronization]
SET [SomeSchema] = DATEADD(s, t.Interval, s.[NextSynchronizationMoment])
FROM [SomeSchema].[Synchronization] s
INNER JOIN #syncs ON #syncs.SynchronizationApiSubscriptionId = s.SynchronizationApiSubscriptionId
INNER JOIN [SomeSchema].[SynchronizationTier] t ON s.SynchronizationTierId = t.SynchronizationTierId

This code sets a new ‘NextSynchronizationMoment’ based on the last moment the sync was run + the interval between syncs, IE: DATEADD(s, t.Interval, s.[NextSynchronizationMoment])

The issue is that there are cases where the past sync moment was so long ago, that we already passed the NextSynchronizationMoment that would be inserted by this code. In that case ONLY, the NextSynchronizationMoment should be DATEADD(s, t.Interval, GETDATE())

So, I made the following change to accomodate this condition:

UPDATE [SomeSchema].[Synchronization]
    SET [NextSynchronizationMoment] = CASE
        WHEN DATEDIFF(s, GETDATE(), s.[NextSynchronizationMoment]) < t.Interval 
        THEN DATEADD(s, t.Interval, s.[NextSynchronizationMoment])--REGULAR SYNC
        ELSE DATEADD(s, t.Interval, GETDATE()--SYNC WAS PREVIOUSLY MISSED, SKIP MISSED SYNCS
    FROM [SomeSchema].[Synchronization] s
    INNER JOIN #syncs ON #syncs.SynchronizationApiSubscriptionId = s.SynchronizationApiSubscriptionId
    INNER JOIN [SomeSchema].[SynchronizationTier] t ON s.SynchronizationTierId = t.SynchronizationTierId

Sadly, this query is marked as invalid on the FROM keyword with little explanation: ‘Incorrect syntax near FROM’. Why this happens is unclear to me. Other solutions on this platform don’t use joins, thus don’t require the FROM keyword. I can’t figure out why this is happening.

How can this query be adapted to adhere to the discussed condition?

>Solution :

You haven’t closed the CASE with an END:

UPDATE [SomeSchema].[Synchronization]
SET [NextSynchronizationMoment] = CASE
        WHEN DATEDIFF(s, GETDATE(), s.[NextSynchronizationMoment]) < t.Interval 
        THEN DATEADD(s, t.Interval, s.[NextSynchronizationMoment])--REGULAR SYNC
        ELSE DATEADD(s, t.Interval, GETDATE())--SYNC WAS PREVIOUSLY MISSED, SKIP MISSED SYNCS
    END -- end the CASE statement ;)
FROM [SomeSchema].[Synchronization] s
INNER JOIN #syncs ON #syncs.SynchronizationApiSubscriptionId = s.SynchronizationApiSubscriptionId
INNER JOIN [SomeSchema].[SynchronizationTier] t ON s.SynchronizationTierId = t.SynchronizationTierId

I find it helps to indent the expressions within the CASE which can help you to identify when you have forgotten to close it with an END.

Use CASE in an UPDATE statement | Microsoft Learn

Also check that you close each bracketed expression that you open, it is pretty common to miss the close bracket with your expression ends with a function call:

DATEADD(s, t.Interval, GETDATE())

The Key to figuring this out is in the Error message itself:

 'Incorrect syntax near FROM'

In SQL error messages like this, near FROM generally means immediately before. So when the error message is generated, it contains the next uncprocessed expression in the query.