CASE expression syntax error SQL

I have researched everywhere but still can't seem to fix a simple error: Running Micros开发者_运维知识库oft SQL server:

UPDATE copyprogmaster
       SET active =
                 CASE
                   WHEN active = 1 THEN active = 0
                   WHEN active = 0 THEN active = 1
                  ELSE active
                 END
WHERE source = 'Mass_Mail'

my error is :

Line 4: Incorrect syntax near '='.

Remove the = after the THEN, so:

  UPDATE copyprogmaster
       SET active =
                 CASE
                   WHEN active = 1 THEN 0
                   WHEN active = 0 THEN 1
                  ELSE active
                 END
  WHERE source = 'Mass_Mail'

You already have active = after the SET on the second line.

You do not need to repeat "active =" after THEN

UPDATE copyprogmaster
       SET active =
                 CASE
                   WHEN active = 1 THEN 0
                   WHEN active = 0 THEN 1
                  ELSE active
                 END
WHERE source = 'Mass_Mail'

Here's an example from the documentation at http://msdn.microsoft.com/en-us/library/ms181765.aspx

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Based on your query, I assume the active field is bit or int (assuming that int field has only values 0, 1, or NULL). In that case, I believe that you can write the query as following:

UPDATE  dbo.copyprogmaster
SET     active = active ^ 1
WHERE   source = 'Mass_Mail'

Notice that the query can handle NULL values and also the rows #1, #4 and #6 in the screenshot are unchanged. Screenshot #1 shows table structure and screenshot #2 displays sample execution of the above query.

Hope that helps.

Screenshot #1:

Screenshot #2: