UPDATE `table1` SET `gender` = IF('f'=`gender`, 'm', 'f');
If you have NULL columns you might want to make another sublevel in IF to
leave it NULL !
I believe this should do it... you might also take into consideraion
removing the possibility of a NULL in the `gender` column... because it
allows the `gender` not to be specified... to be null !
--
Gabriel PREDA
Senior Web Developer
On 2/10/06, Veerabhadrarao Narra <[EMAIL PROTECTED]>
wrote:
>
>
> I have a table named table1 structure is
>
> +--------+---------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------+---------------+------+-----+---------+-------+
> | name | varchar(50) | NO | PRI | | |
> | gender | enum('f','m') | YES | | NULL | |
> +--------+---------------+------+-----+---------+-------+
>
> And Values like
>
> +------+--------+
> | name | gender |
> +------+--------+
> | 1 | m |
> | 2 | m |
> | 3 | m |
> | 4 | m |
> | 5 | m |
> | 6 | m |
> | 7 | m |
> | 8 | m |
> | 9 | m |
> | 91 | f |
> | 92 | f |
> | 93 | f |
> | 94 | f |
> | 95 | f |
> | 96 | f |
> | 97 | f |
> | 98 | f |
> | 99 | f |
> +------+--------+
>
> Now i want to change the values in gender column
> from 'm' to 'f' as well as 'f' to 'm' in single UPDATE statement. How can
> we write this. And i have check constraint it accept only 'f' or 'm'.
> (Means name 1 to 9 gender have to change 'f' and 91 to 99 'm')
>
