Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    8

    Question Unanswered: UPDATE with CASE Not Working

    I'm trying to UPDATE a column based on set criteria using the CASE statement. The update statement below updates every single record in the table instead of just the records that should be updated. However, If I do a select using the same criteria below I get the correct results.

    UPDATE Monthly SET Campaign = CASE
    WHEN LEFT(Zip1, 5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK)) AND TotalTrips >= 36 AND
    LastActivity > DATEADD(month, -3, GetDate()) THEN 'Ultra High'
    WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK)) AND TotalTrips BETWEEN 24 AND 35 AND
    LastActivity > DATEADD(month, -3, GetDate()) THEN 'High'
    WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK)) AND TotalTrips < 24 AND
    LastActivity > DATEADD(month, -3, GetDate()) AND
    Birthday < DATEADD(year, -40,GetDate()) THEN 'Low'
    WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK)) AND TotalTrips < 24 AND
    LastActivity > DATEADD(month, -3, GetDate()) AND
    Birthday > DATEADD(year, -40,GetDate()) THEN 'Ultra Low'
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let's see if some formatting will help you to see the problem:
    Code:
    UPDATE Monthly
       SET Campaign =
          CASE
             WHEN LEFT(Zip1, 5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips >= 36
                AND LastActivity > DATEADD(month, -3, GetDate()) THEN 'Ultra High'
             WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips BETWEEN 24 AND 35
                AND LastActivity > DATEADD(month, -3, GetDate()) THEN 'High'
             WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips < 24
                AND  LastActivity > DATEADD(month, -3, GetDate())
                AND Birthday < DATEADD(year, -40,GetDate()) THEN 'Low'
             WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips < 24
                AND LastActivity > DATEADD(month, -3, GetDate())
                AND Birthday > DATEADD(year, -40,GetDate()) THEN 'Ultra Low'
          END
    I'd bet that you were thinking that you had a WHERE clause in there somewhere.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2009
    Posts
    8
    PatP,

    Thanks for the reply. Unfortunately the formatting doesn't help me identify the problem. Even if I do a simple evaluation like the one below it updates every record.

    UPDATE Monthly
    SET Campaign = CASE
    WHEN LEFT(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
    END

    But if I do a

    SELECT COUNT (*) FROM Monthly
    WHERE LEFT(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))

    I get the correct number of results. Is the CASE function limited in some way that won't allow it to process a nested query?

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dlg, Pat answered your question. Your Select has a Where clause. Your Update does not. The Where clause filters out the rows that you want displayed.

    The CASE expression in your Update is NOT doing any filtering. It is being used to determine what to set CAMPAIGN to for each row of the table (since there is no WHERE clause). Since you do not have an ELSE in you CASE expression, I believe this is setting CAMPAIGN to NULL for those rows that do not match any of the WHEN processing.

    To Update only the rows that you want you would need something like this (I just took the WHEN clauses and put then in OR 'chunks' in a Where clause. I didn't analyze them much to see if they could be simplified / optimized):
    Code:
    UPDATE Monthly
       SET Campaign =
          CASE
             WHEN LEFT(Zip1, 5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips >= 36
                AND LastActivity > DATEADD(month, -3, GetDate()) THEN 'Ultra High'
             WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips BETWEEN 24 AND 35
                AND LastActivity > DATEADD(month, -3, GetDate()) THEN 'High'
             WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips < 24
                AND  LastActivity > DATEADD(month, -3, GetDate())
                AND Birthday < DATEADD(year, -40,GetDate()) THEN 'Low'
             WHEN Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
                AND TotalTrips < 24
                AND LastActivity > DATEADD(month, -3, GetDate())
                AND Birthday > DATEADD(year, -40,GetDate()) THEN 'Ultra Low'
          END
    WHERE ( 
               LEFT(Zip1, 5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
           AND TotalTrips >= 36
           AND LastActivity > DATEADD(month, -3, GetDate()) THEN 'Ultra High'
          )
       OR
          (
               Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
           AND TotalTrips BETWEEN 24 AND 35
           AND LastActivity > DATEADD(month, -3, GetDate()) THEN 'High'
          )
       OR
          (
               Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
           AND TotalTrips < 24
           AND  LastActivity > DATEADD(month, -3, GetDate())
           AND Birthday < DATEADD(year, -40,GetDate()) THEN 'Low'
          )
       OR
          (
               Left(Zip1,5) IN (SELECT ZipCode FROM tmp_Local_Zip (NOLOCK))
           AND TotalTrips < 24
           AND LastActivity > DATEADD(month, -3, GetDate())
           AND Birthday > DATEADD(year, -40,GetDate()) THEN 'Ultra Low'
          )

  5. #5
    Join Date
    Jul 2009
    Posts
    8
    Thanks guys for the assistance. I wasn't aware that i could use a WHERE clause with the CASE statement. I appreciate the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •