Jump to content

Archived

This topic is now archived and is closed to further replies.

NevadaSam

Multiple UPDATE to SameColumn (with different conditions)

Recommended Posts

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

Share this post


Link to post
Share on other sites
You can use a CASE statement.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
No, MySQL has a CASE statment.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.