CODEWITHSUNDEEP
sqlsql-serversql-server-2005syntax
JDV590
JDV590

Reputation: 651

CASE expression syntax error SQL

I have researched everywhere but still can't seem to fix a simple error: Running 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 error is :

Line 4: Incorrect syntax near '='.

Upvotes: 4

Views: 1374

Answers (3)

user756519
user756519

Reputation:

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:

Table

Screenshot #2:

Output

Upvotes: 1

tofutim
tofutim

Reputation: 23374

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

Upvotes: 4

JNK
JNK

Reputation: 65147

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.

Upvotes: 10

Related Questions