Mulltiple UPDATEs in a single statement - HTML Forums

cmetz1977 · 10-08-2009, 10:28 AM

Is there a way to run multiple UPDATE operations within a single statement, using independant criteria? Hypothetical syntax:

PHP Code:


			
UPDATE street_abbreviations

   (SET abbreviation = 'Blvd' WHERE street = 'Boulevard')

AND

   (SET abbreviation = 'Ln' WHERE street = 'Lane')

Horus_Kol · 10-08-2009, 11:38 PM

The best I can come up with after a short bit of testing is:

Code:

UPDATE street_abbreviations SET abbreviation = street;

UPDATE street_abbreviations SET abbreviation = REPLACE(abbreviation, 'Boulevard', 'Blvd'), abbreviation = REPLACE(abbreviation, 'Lane', 'Ln');

I did try simply:

Code:

UPDATE street_abbreviations SET abbreviation = REPLACE(street, 'Boulevard', 'Blvd'), abbreviation = REPLACE(street, 'Lane', 'Ln');

but that would only work for the last match...

cmetz1977 · 10-09-2009, 09:08 AM

I had assumed you could only do one update at a time, but figured it would be faster to do multiple updates in one statement as compared to one update per statement in multiple statements.

Thank you.