Jump to content


Photo

Multiple UPDATE to SameColumn (with different conditions)


  • Please log in to reply
6 replies to this topic

#1 NevadaSam

NevadaSam
  • Members
  • PipPip
  • Member
  • 26 posts
  • LocationUSA

Posted 29 September 2006 - 12:06 AM

I know to change the data in a I simply send an update querry:

UPDATE foods
SET SameColumn = 'Red Peas'
WHERE SameColumn = 'rpeas'


But I have several more categories in that SameColumn that I also need to change.

SET SameColumn = 'Whole Milk'
WHERE SameColumn = 'wmilk'

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

SET SameColumn = 'Tatar Tots'
WHERE SameColumn = 'ttots'

Is there a way I can send them all at once or will each have to be done one at a time?

Sam



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 September 2006 - 02:54 AM

You can use a CASE statement.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 NevadaSam

NevadaSam
  • Members
  • PipPip
  • Member
  • 26 posts
  • LocationUSA

Posted 29 September 2006 - 03:02 AM

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


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 September 2006 - 03:03 AM

No, MySQL has a CASE statment.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 NevadaSam

NevadaSam
  • Members
  • PipPip
  • Member
  • 26 posts
  • LocationUSA

Posted 29 September 2006 - 11:00 PM


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

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

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



#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 September 2006 - 03:51 PM

There is an ELSE:

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

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 NevadaSam

NevadaSam
  • Members
  • PipPip
  • Member
  • 26 posts
  • LocationUSA

Posted 30 September 2006 - 04:33 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users