List:General Discussion« Previous MessageNext Message »
From:Benjamin Pflugmann Date:July 7 1999 7:54pm
Subject:How to delete a member from a SET column
View as plain text  
Hi.

While 'playing' around with SETs I did not like how to handle them (at
least the ways I found).

Presume an application which does not know about all currently allowed
members of the SET, but only the ones it really needs. I really want
atomic statements, so 'DESCRIBE table' and do some parsing in a
programming language is out of the scope.

Say I have a SET like (examples tested with MySQL 3.22.16b)

DROP TABLE test IF EXISTS;
CREATE TABLE test ( field SET ('one', 'two', 'three', 'four') );
INSERT INTO test VALUES ('one,three');
INSERT INTO test VALUES ('');
SELECT * FROM test;

Ok, I can add abitrary members of the set by using

UPDATE test SET field=CONCAT(field,',one,two');
SELECT * FROM test;

This is quite nice, except that it produces WARNINGs for empty values
and that the ',' is needed before 'one' (cannot count how often I
forgot it the last two days). Now, what is the way to do removing?  I
could do something like

UPDATE test SET field=REPLACE(REPLACE(field,'one',''),'two','');
SELECT * FROM test;

but I really don't like it, especially if more members have to be
removed. Again, it produces a WARNING for each ',' left alone
removed. Using the field two times IMHO is somewhat prettier, but also
a bit slower (about 15% in my tests):

UPDATE test SET field=REPLACE(field,'one',''),
                field=REPLACE(field,'two','');


I guess, something like

UPDATE test SET field=STRIP(field, 'one', 'two');

(or STRIP(field, 'one,two')) 
would be nice (choose whatever name you like for STRIP).
STRIP(field,'one,two'), would not work if STRIP would be 'only' a
string function (since 'one' and 'two' do not have to appear as
'one,two' in the SET).

Or am I just blind and someone knows a better and clean (i.e. without
WARNING) way to archieve the same just with MySQL?

Bye,

        Benjamin.

PS: I know that I can get rid of the WARNINGs by applying
TRIM(LEADING ',' FROM CONCAT(field,',one,two')) and
TRIM(BOTH ',' FROM REPLACE(REPLACE(REPLACE(field,'one',''),'two',''),',,',','))
or something like that, but I hope nobody seriously suggests to use it
that way. (Hm. The second line does not even catch all possibilities yet...)


Attachment: [application/pgp-signature]
Thread
How to delete a member from a SET columnBenjamin Pflugmann7 Jul
  • Re: How to delete a member from a SET columnMartin Ramsch15 Sep
    • Re: How to delete a member from a SET columnJames Manning15 Sep
      • Re: How to delete a member from a SET columnMartin Ramsch16 Sep
        • Re: How to delete a member from a SET columnMartin Ramsch16 Sep
          • Re: How to delete a member from a SET columnJames Manning17 Sep
            • Re: How to delete a member from a SET columnMartin Ramsch17 Sep
              • Re: How to delete a member from a SET columnJames Manning17 Sep
                • Re: How to delete a member from a SET columnMartin Ramsch17 Sep
                  • placeholdersjice17 Sep
                    • Re: placeholdersBob Kline17 Sep
                  • Re: How to delete a member from a SET columnJames Manning17 Sep
        • Re: How to delete a member from a SET columnJames Manning17 Sep
          • Re: How to delete a member from a SET columnMartin Ramsch17 Sep