How about this? update t set onoffflag = if (name <> 'China', onoffflag, ( if (location = 'Table', 1, 0) ) );
This leaves any onoffflag untouched if name is not China, which I assume you wanted to do. On Thu, Jan 8, 2009 at 2:18 PM, Christoph Boget <christoph.bo...@gmail.com>wrote: > Consider the folowing dataset: > > +----+------------+-------------+-------------+---------------+ > | id | Name | Location | OnOffFlag | Description | > +----+------------+-------------+-------------+---------------+ > | 1 | Paper | Cabinet | 0 | Blah | > | 2 | Plastic | Cabinet | 0 | Blah | > | 3 | China | Cabinet | 1 | Blah | > | 4 | Glass | Cabinet | 0 | Blah | > | 5 | China | Table | 0 | Blah | > | 6 | China | Cabinet | 1 | Blah | > +----+------------+-------------+-------------+---------------+ > > Is there a way to, using a single query, set the OnOffFlag to 1 for > the record that matches [Name=China AND Location=Table] at the same > time setting the OnOffFlag to 0 for records that match [Name=China AND > Location!=Table]? I know I can do it in 2 queries but I am curious to > know if it can actually be done in 1. > > thnx, > Chris > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
