Single query "radio button" selection behaviour with UPDATE

I am using MS SQL CE 3.5 in an desktop application (Windows Forms C# 4开发者_JAVA技巧) and is unlikely to change database. I sometimes hit a brick wall in the more limited SQL syntax of Compact Edition.

Currently I need two queries but would like to join them into one:

UPDATE manganames SET display=0 WHERE id=@id AND display=1

UPDATE manganames SET display=1 WHERE id=@id AND name=@name

display is datatype BIT

Thank you, my question was brief rather than completely clear

Final query follows

UPDATE manganames

SET display = CASE WHEN name=@name THEN 1 ELSE 0 END

WHERE id=@id

PS My stackoverflow formating skills sucks.

Here's something you can try

UPDATE manganames
SET display =
    CASE WHEN display = 1 then 0
         WHEN name = @name
         ELSE display
    END
WHERE id = @id

There are a couple of conditions that you don't explain. First, what should happen when display = 1 and name = name? With what you show, both 0 and 1 would be assigned. In my example, the first WHEN clause would apply and 0 will be assigned to display.

Second, what should happen when display <> 1 and name <> @name? In my example, the ELSE condition will assign display to display, which results in no change.