CODEWITHSUNDEEP
sql-servert-sql
Melinda
Melinda

Reputation: 1531

What WHERE clause to use in UPDATE statement based on variable value

I have an UPDATE statement where I've setup a variable @peims2017SnapshotDate and depending on its value, I need to determine which WHERE clause to use.

I'm getting an IntelliSense error in SQL Server 2014 after @ISTRUE = 1, where the word WHERE has incorrect syntax errors.

I'm sure there is a better way to handle this but I'm in the weeds here.

Here is my SQL code:

    UPDATE t
    SET t.Enddate = CONVERT(VARCHAR(10), DATEADD(day, -1, s.Effective_Date),101),
        t.LastUpdated = GETDATE(),
        t.UpdatedAppType = 'HHS_update',
        t.DataSource = 'HHS' 
    FROM dbo.vw_HHSurvey s
    INNER JOIN dbo.Focus_Econ_Disadvantaged_Last_Snapshot t
        ON s.CustomerID = t.StudentID
        AND s.SiteID = t.Location
    SET @ISTRUE = (SELECT CASE WHEN CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate THEN 1 ELSE 0 END) 
    IF @ISTRUE = 1  
        WHERE CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate) 
        AND CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
        AND ((t.[Status] = 'Paid' AND s.[Status] != 'PAID') 
            OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE'))
    IF @ISTRUE = 0 
        WHERE CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
        AND ((t.[Status] = 'Paid' AND s.[Status] != 'PAID') 
            OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE'))

Upvotes: 0

Views: 146

Answers (4)

Oreo
Oreo

Reputation: 555

You cannot SET variables in the middle of ON filters.

Try the below:

UPDATE t
SET t.Enddate = CONVERT(VARCHAR(10), DATEADD(day, -1, s.Effective_Date),101),
    t.LastUpdated = GETDATE(),
    t.UpdatedAppType = 'HHS_update',
    t.DataSource = 'HHS' 
FROM dbo.vw_HHSurvey s
INNER JOIN dbo.Focus_Econ_Disadvantaged_Last_Snapshot t
    ON s.CustomerID = t.StudentID
    AND s.SiteID = t.Location
WHERE (
    CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate 
    AND CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
    AND ((t.[Status] = 'Paid' AND s.[Status] != 'PAID') 
        OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE')
        )
    OR
    NOT(CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate)
    AND CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
    AND ((t.[Status] = 'Paid' AND s.[Status] != 'PAID') 
        OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE')
        )
    )

Upvotes: 3

nejcs
nejcs

Reputation: 1262

While others have already pointed out that you cannot use SET in a middle of UPDATE statement, and you generally have to either do check twice and add OR between alternatives or be a little more creative with CASE, here is your query further simplified by the fact that WHERE condition is exactly the same for both conditions, only difference is that you do additional check if condition is TRUE:

UPDATE t
SET t.Enddate = CONVERT(VARCHAR(10), DATEADD(day, -1, s.Effective_Date),101),
    t.LastUpdated = GETDATE(),
    t.UpdatedAppType = 'HHS_update',
    t.DataSource = 'HHS' 
FROM dbo.vw_HHSurvey s
INNER JOIN dbo.Focus_Econ_Disadvantaged_Last_Snapshot t
ON s.CustomerID = t.StudentID
AND s.SiteID = t.Location
AND CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
AND ((t.[Status] = 'Paid' AND s.[Status] != 'PAID') 
    OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE'))
AND (CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate AND CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate
    OR CAST(t.StatusEffectiveDate as date) <= @peims2017SnapshotDate)

Upvotes: 0

HLGEM
HLGEM

Reputation: 96600

Since some of your conditions work both when @ISTRUE is 1 or 0, I have simplified a bit. Be sure to check that your results are the same when you try this instead.

DECLARE @ISTRUE  int, @peims2017SnapshotDate datetime
SET @peims2017SnapshotDate = getdate()-- note this was placed here just to see if the syntax passed, use whatever declaration and population you have for this variable
SET @ISTRUE = (SELECT
    CASE
    WHEN CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate 
    THEN 1 ELSE 0 END);

UPDATE t
SET t.Enddate = CONVERT(VARCHAR(10), DATEADD(day, -1, s.Effective_Date),101),
    t.LastUpdated = GETDATE(),
    t.UpdatedAppType = 'HHS_update',
    t.DataSource = 'HHS' 
FROM dbo.vw_HHSurvey s
INNER JOIN dbo.Focus_Econ_Disadvantaged_Last_Snapshot t
    ON s.CustomerID = t.StudentID
    AND s.SiteID = t.Location
WHERE CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
    AND (
            (t.[Status] = 'Paid' AND s.[Status] != 'PAID')      
            OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE')
        )
    and (
            (
            @ISTRUE = 1  
            AND CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate 
            )
            OR @ISTRUE = 0
        );

Upvotes: 0

IngoB
IngoB

Reputation: 2989

  UPDATE t
     SET t.Enddate = CONVERT(VARCHAR(10), DATEADD(day, -1, s.Effective_Date),101),
         t.LastUpdated = GETDATE(),
         t.UpdatedAppType = 'HHS_update',
         t.DataSource = 'HHS' 
    FROM dbo.vw_HHSurvey s
   INNER JOIN dbo.Focus_Econ_Disadvantaged_Last_Snapshot t ON s.CustomerID = t.StudentID AND s.SiteID = t.Location
   WHERE 1 = CASE
              WHEN CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate
                 THEN CASE
                       WHEN     CAST(t.StatusEffectiveDate as date) > @peims2017SnapshotDate) 
                            AND CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
                            AND (   (t.[Status] = 'Paid'    AND s.[Status] != 'PAID') 
                                 OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE')) then 1
                       ELSE 0
                      END
              ELSE CASE
                    WHEN     CONVERT(date, s.Effective_Date, 101) > CONVERT(date, t.StatusEffectiveDate, 101)
                         AND (   (t.[Status] = 'Paid' AND s.[Status] != 'PAID') 
                              OR (t.[Status] = 'Reduced' AND s.[Status] = 'FREE')) then 1
                    ELSE 0
                   END
             END

Upvotes: 0

Related Questions