[RESOLVED] setting all records apart from one

[dst101]

I am trying figure out how to set all records to 0 apart from one record, this is to be set to 1.
i.e
I have a bunch of images/records each assigned a radio button.
The user should pick one to feature on the front page.
But all the other records must equal 0 apart from the user's chosen image/record this is to be 1.
Can't seem to get my head round it to come up with a successful sql statement.

Can anyone suggest anything?

Thanks

[laserlight]

You might be looking for something along the lines of:

UPDATE tablename SET column=1 WHERE row_id=N;
UPDATE tablename SET column=0 WHERE row_id<>N;

[dst101]

Thanks.

is there anyway of writing this as one statement rather than two?

[dst101]

UPDATE yourspa_portfolio SET featureFrontPage =

CASE id
WHEN id = $id THEN '1'
ELSE '0'
END

I thought about this but it doesnt seem to work, any ideas??

[dst101]

No one?

I could really do with someone's help on this....

[Lars_Berg]

UPDATE yourspa_portfolio 
  SET featureFrontPage = CASE WHEN id = $id THEN 1 ELSE 0 END

[dst101]

Thanks Lars!