Thanks, I'll take a look at that. Appreciate the help, Waynn On Sat, Jul 12, 2008 at 9:02 PM, Rob Wultsch <[EMAIL PROTECTED]> wrote:
> 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] >
