On Sat, Jul 12, 2008 at 8:01 PM, Waynn Lue <[EMAIL PROTECTED]> wrote: > Is there any way to have an UPDATE statement change a column value based on > the WHERE statement? > > Essentially, I want to do something like this > > UPDATE Actions SET ActionsSent = <foo> WHERE ActionsReceived = <bar> > > where foo and bar change for multiple sets of values. Is there a way to do > this in one SQL statement, or do I have to execute an UPDATE statement per > pair of values? > > Thanks, > Waynn >
Take a look at CASE http://dev.mysql.com/doc/refman/5.0/en/case-statement.html mysql> CREATE TABLE `t2` ( -> `col1` varchar(255) default NULL, -> `col2` varchar(255) default NULL -> ); Query OK, 0 rows affected (0.13 sec) mysql> INSERT INTO t2 (col1,col2) VALUES(null, 'stuff'),(null, 'foo'),(null, 'bar'); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> UPDATE t2 SET col1 = CASE `col2` -> WHEN 'stuff' THEN 'blah' -> WHEN 'foo' THEN 'darn foo' -> ELSE 'not blah' -> END; Query OK, 3 rows affected (0.06 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> SELECT * FROM t2; +----------+-------+ | col1 | col2 | +----------+-------+ | blah | stuff | | darn foo | foo | | not blah | bar | +----------+-------+ 3 rows in set (0.00 sec) -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
