Dear Nick,
I dislike SET and ENUM as they are not SQL standard (tell me if I am
wrong). They may be fast because of the bitwise storage of data. But
both column types mangle DDL with DML, and that's where problems arise,
like yours.
Suggestion: Better go the straight way, i. e. put those flags into a
different table. You will have to JOIN where you did not have to join
before, but on the long run it will make things much easier, and more
transparent.
Instead of
> UPDATE users SET user_flags = 'test, none' WHERE user_id = 1
this will be
UPDATE user_flags SET flag = "test" WHERE user_id = 1
Hope it helps.
--
Stefan Hinz
Geschäftsführer / CEO iConnect e-commerce solutions GmbH
# www.js-webShop.com www.iConnect.de
# Gustav-Meyer-Allee 25, 13355 Berlin
# Tel: +49-30-46307-382 Fax: +49-30-46307-388
----- Original Message -----
From: "Nick Brandon" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 19, 2001 8:24 PM
Subject: UPDATEing values in SET columns
> Hi
>
> I have been searching through the manual and the list archives for the
last
> few days regarding a problem I have with the SET column type.
>
> I'm creating an app that uses a SET column as 'flags'. Different parts
> within the application will check to see if certain 'flags' are
enabled. As
> I understand, the SET column uses bit representation internally.
>
> My problem is updating the column with flags already set. Below is an
> example:
>
> The table is as follows:
>
> CREATE TABLE users ...
> user_id INT ...
> user_flags SET('another','test','pass_renew','none') NOT NULL DEFAULT
> 'none'
> ...
>
> When I update a record, I can only set the whole string not just the
> individual flag.
>
> i.e. This works fine...
>
> UPDATE users SET user_flags = 'test, none' WHERE user_id = 1
>
> How can I add (or remove) values within the SET column without
affecting the
> existing contents?
>
> I've tried a number of ways with no success!!
> For example
>
> UPDATE users SET user_flags = CONCAT(user_flags,'pass_renew') WHERE
user_id
> = 1
>
> Any help or pointers would be greatly appreciated.
>
> Thanks
>
> Nick Brandon
> P.S. Please cc my email address as I'm not subscribe to the list -
Thanks
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
