How do I negate a value in a SET clause for SQL Server Compact?

Question

UPDATE [MyTable] 
SET [MyBoolColumn] = NOT [MyBoolColumn]

That doesn't work on SQL Server CE. Error is

There was an error parsing the query. [Token in error = NOT]

So how do I get this to work?

Answer 1

DECLARE @MyTable TABLE (MyBoolColumn BIT)

INSERT INTO @MyTable (MyBoolColumn)
VALUES (0), (1)

UPDATE @MyTable
SET MyBoolColumn = ~MyBoolColumn

SELECT * FROM @MyTable

results -

------------
1
0