CASE SQL expression syntax error

Question

I researched everywhere, but still can't fix a simple error: Starting Microsoft 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 mistake:

Line 4: Incorrect syntax next to '='.

Answer 1

Remove = after THEN , therefore:

  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 SET in the second line.

Answer 2

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 is 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 

Answer 3

According to your request, I assume that the active field is bit or int (assuming the int field has only 0 , 1 , or NULL ). In this case, I believe that you can write the query as follows:

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

Please note that the request can process NULL values, as well as lines # 1 , # 4 and # 6 in the screenshot. without changes. Screenshot # 1 shows the structure of the table and screenshot # 2 shows an example of the execution of the above query.

Hope this helps.

Screenshot # 1:

Screenshot No. 2: