How should I update a column of type 'set'?

Databases
1

Problem solved (see below). it was the spaces after the commas in the list. The following DOES work:

UPDATE listings SET `facilities` = 'child,cot,ground,wifi,allyear' WHERE `bus_id` = 2073

My original posting can now be ignored:
I have a column of type ‘SET’, which contains the following values (pasted from ‘structure’):

set('access', 'active', 'allday', 'child', 'cot', 'card', 'dinner', 'disabled', 'food', 'ground', 'allyear', 'partday', 'licensed', 'lunch', 'pets', 'static', 'wet', 'wifi')

I want to update it, but can’t quite get the syntax right. I have looked at the MySQL documentation, but haven’t found the answer. I have tried:

UPDATE listings SET `facilities` = 'cot, child, ground, wifi, allyear' WHERE `bus_id` = 2073

This only sets the first value (cot), but at least it doesn’t give an error message.
I have tried other variants such as

UPDATE listings SET `facilities` = 'cot', 'child', 'ground', 'wifi', 'allyear' WHERE `bus_id` = 2073

which gives an error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''child', 'ground', 'wifi', 'allyear' WHERE `bus_id` = 2073' at line 1

and

UPDATE listings SET `facilities` = ('ground, cot, child, wifi, allyear') WHERE `bus_id` = 2073

which again only updates the first value (in this case ‘ground’), but no error message.
I hope I’m close to the right syntax, but it still eludes me. Can anyone help, please ?
Once I’ve cracked this, I hope to go on and update the column without unsetting the values already present.

2

I don’t see why this:


UPDATE 
  listings 
  SET facilities = 'cot, child, ground, wifi, allyear' 
  WHERE bus_id = 2073

wouldn’t work. Works fine for me. What is the data type for the column facilities and how many characters you have set for this column?

3

Thanks for your response.
I tried it as you suggest, but it would NOT work (except for the first item) while there were spaces after the commas. As soon as I removed the spaces, it worked fine. So:

SET facilities = 'cot, child, ground, wifi, allyear'

is bad (although it doesn’t throw an error message), but

SET facilities = 'cot,child,ground,wifi,allyear'

is good.
Of course I didn’t work this out until five minutes after I’d posted my original thread, hence the edit.

4

The question remains! What is the data type in use and what is the number of characters?.

5

hint: check the thread title

6

Question: Is that appropriate?

7

I didn’t answer your question because I had resolved the problem I was having with the syntax, and I didn’t think the answer was going to be relevant.
The data type is of course ‘set’, and the values I was trying to update are included in that set. IOW I wasn’t trying to set an illegal value. I did list the entire set of values in my original posting. A ‘set’ data type takes its total number of characters from the values in the set, it is not necessary to set the number explicitly.
Has that answered the question, or am I missing something ?

8

That explains a lot. I indeed didn’t read the title of your post properly (As Rudy(r937) noticed), but seeing your updates I used varchar instead.

Mainly because of this comment on dev.mysql.com

Why You Shouldn’t Use SET

The MySQL SET datatype is not commonly used for a few reasons; First, using the MySQL SET datatype limits you to 64 elements. While you could get around this by using multiple SETs, this still represents a loss of versatility. Second, you cannot include commas in your set elements, as the comma is an element separator. Third, using a set means your data is not normalized. In our above example, we are tracking a person’s interests for a hypothetical dating site. In a normalized schema, there should be three tables: one for the person, one for all possible interests, and one that links a person to their particular interests. Fourth, an INDEX on a set datatype is going to refer to the set as a whole and will not be used for searching individual elements (this may or may not be a problem for certain applications).

So why do we use the MySQL SET datatype? Well there are a few reasons; The MySQL SET datatype allows us to handle multiple values with a lot less trouble than if we had our table fully normalized. Our schema is simplified because we only need one column instead of three tables to store our list of interests. The MySQL SET datatype allows us to compare multiple values without using complex JOIN operations. We can manipulate the set with binary functions to do complex comparisons by comparing bit values on a single column instead of comparing multiple rows of multiple tables.

You may find that the MySQL SET datatype is something that you never need to use. If you find that it’s advantages outweigh the disadvantages, read on to discover how to use it.

9

Yes ! I’m aware of the limitations of the SET data type, but felt it worth using for the reasons given in the second paragraph of your quote. That was a year ago. I may now be wading deeper into these limitations, so my better option might be to create another table. I’m not as scared of joins as I was then (though I’m still a bit confused by the different types).
I do have Rudy’s book as a PDF, so I’d better go and read it.
Thanks for your interest.