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.