Logic problem

Hi,

This might just be my brain not working after the weekend, but I'm having problems working out just how to do this.

The table:

CREATE TABLE [dbo].[tblQuiz] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[q1] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q2] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q3] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q4] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q5] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q6] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q7] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[q8] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[quizdate] [datetime] NULL ,
[ipaddress] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sessionid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[score] [int] NULL
)

The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".

I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then:
if [q5]= 'Happy' then change it to 'Unhappy'
if [q5]= 'Unhappy' then change it to 'Happy'

The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?Is this an abstraction of a real world problem?
Anyway - check out CASE in BoL - it is just the ticket for you.|||Is this an abstraction of a real world problem?
Anyway - check out CASE in BoL - it is just the ticket for you.

I'm not sure what you mean by abstraction? It IS a real-world problem: "Real" as in "my boss is swearing at me". :eek:

But you're right: CASE does the job perfectly. Thankyou very much :)|||Generally it's better to use a lookup table to store your Happy/Unhappy/Neither strings and refer to them through fk ids in tblQuiz. That way you don't waste space storing the same string over and over.

If there are truly only 3 possible values, you could be using a tinyint in the q5 column instead of nvarchar(100), which is a pretty big space savings. this begins to matter pretty quickly in large databases with millions of rows.