SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    350
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Problem solved (see below). it was the spaces after the commas in the list. The following DOES work:
    Code:
    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'):
    Code:
    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:

    Code:
    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
    Code:
    UPDATE listings SET `facilities` = 'cot', 'child', 'ground', 'wifi', 'allyear' WHERE `bus_id` = 2073
    which gives an error message:
    Code:
    #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
    Code:
    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.
    Last edited by ramasaig; Jan 11, 2011 at 19:30. Reason: I found the solution
    Tim Dawson
    Isle of Mull, Scotland

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't see why this:
    Code MySQL:
    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?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  3. #3
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    350
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    SET facilities = 'cot, child, ground, wifi, allyear'
    is bad (although it doesn't throw an error message), but
    Code:
    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.
    Tim Dawson
    Isle of Mull, Scotland

  4. #4
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The question remains! What is the data type in use and what is the number of characters?.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    The question remains! What is the data type in use and what is the number of characters?.
    hint: check the thread title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    hint: check the thread title
    Question: Is that appropriate?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  7. #7
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    350
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by donboe View Post
    The question remains! What is the data type in use and what is the number of characters?.
    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 ?
    Tim Dawson
    Isle of Mull, Scotland

  8. #8
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  9. #9
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Fionnphort, Isle of Mull, Scotland
    Posts
    350
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Tim Dawson
    Isle of Mull, Scotland


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •