Flipping answers in a table.

Hello all,

I have a simple task that I need to switch all the answers in my tables from yes(11) to no(10) and no to yes. It is simple enough to just make three update statements that will do this but is there a more elegant way to do this swap from one update statment.

Here are the three update statments:
update ans_test
set ans_chc_id = 99
where ans_chc_id = 11

update ans_test
set ans_chc_id = 11
where ans_chc_id = 10

update ans_test
set ans_chc_id = 10
where ans_chc_id = 99

Use CASE statements to allow a single update statement to do the work. Something like:

Code Snippet

update ans_test
set ans_chc_id
= case when ans_chc_id = 11 then 10
else 11
end
--where ans_chc_id between 10 and 11

The WHERE statement is necessary ONLY if the column contains values other than 10 and 11 and you do not want the row update in that particular case.

|||Excellent, thank you very much for your help.
|||

This 'seems' somewhat 'odd'. Normally a Yes/No question 'should' have three responses: Yes/No/Unknown. And equally often the data is stored as 0/1/NULL. I don't get values like 10, 11, and 99. It seems like you are really making things unnecessarily difficult for youself.

However, expanding on Kent's suggestion, you can handle the flipping 'flipping' with a CASE structure similar to this:

Code Snippet

UPDATE Ans_Test
SET Ans_Chc_ID = CASE
WHEN 10 THEN 99
WHEN 11 THEN 10
WHEN 99 THEN 11
END
WHERE {criteria}