Multiple UPDATE to SameColumn (with different conditions)

[NevadaSam]

I know to change the data in a I simply send an update querry:[quote]UPDATE foods
SET SameColumn = 'Red Peas'
WHERE SameColumn = 'rpeas'[/quote]

But I have several more categories in that SameColumn that I also need to change.
[quote]SET SameColumn = 'Whole Milk'
WHERE SameColumn = 'wmilk'

SET SameColumn = 'T-Bone Steak'
WHERE SameColumn = 'tsteak'

SET SameColumn = 'Tatar Tots'
WHERE SameColumn = 'ttots'
[/quote]
Is there a way I can send them all at once or will each have to be done one at a time?

Sam

[fenway]

You can use a CASE statement.

[NevadaSam]

The case statement would have to be ran from within a php script right? I am new at this but I think I can figure that out. Thanks! I was hoping that it would be something that could work from a query by adding ANDs or something.

I'll let you know how I do tomorrow.

Sam

[fenway]

No, MySQL has a CASE statment.

[NevadaSam]

Thanks for telling me about the CASE statement, fenway. This works. The first set of WHEN - THEN statements does the trick.

[code]UPDATE thisTable

SET thisColumn = CASE thisColumn

WHEN 'd1' THEN 'Data One'
WHEN 'd2' THEN 'Data Two'
WHEN 'd3' THEN 'Data Three'
WHEN 'd4' THEN 'Data Four'


WHEN 'Data One' THEN 'Data One'
WHEN 'Data Two' THEN 'Data Two'
WHEN 'Data Three' THEN 'Data Three'
WHEN 'Data Four' THEN 'Data Four'

END[/code]

I had to add the second set of WHEN - THEN statements because if the query was ran after the new values were set the field would be set as NULL if the old WHEN value was not found.

Is there a way to express an ELSE statement so I would not have to use the second set of WHEN - THEN statements?

Sam

[fenway]

There is an ELSE:

[code]
UPDATE thisTable

SET thisColumn = CASE thisColumn

WHEN 'd1' THEN 'Data One'
WHEN 'd2' THEN 'Data Two'
WHEN 'd3' THEN 'Data Three'
WHEN 'd4' THEN 'Data Four'
ELSE thisColumn
END
[/code]

[NevadaSam]

Thanks, I knew the ELSE statement could be used. I just was not sure what to say with it.