Another Inserting Multiple Values with Set Problem

--001636417375153554047c7f6f45
Content-Type: text/plain; charset=ISO-8859-1

Hi;
The following doesn't work with or without enclosing the sets in quotes:

update products set SKU="prodSKU2", Category="prodCat1", Name="name2",
Title="title2", Description="descr", Price="22.55", SortFactor="500",
Availability="1", OutOfStock="0", Weight="5.5", ShipFlatFee="10.0",
ShipPercentPrice="5", ShipPercentWeight="2", sizes="('Extra-small', 'Large',
'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge')",
colorsShadesNumbersShort="('aqua:7FFFD4', 'blue:333399', 'gray:465945',
'navy-blue:CC7722', 'black:0000FF', 'maroon:B03060', 'purple:50404D',
'yellow:9ACD32', 'fuchsia:FF77FF')" where ID="2";

Everything inserts but the sets which throw warnings. What am I missing?
TIA,
Victor

--
The Logos has come to bear
http://logos.13gems.com/

--001636417375153554047c7f6f45--

Re: Another Inserting Multiple Values with Set Problem

--000e0cd5cc72228329047c80f41b
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jan 6, 2010 at 10:56 AM, Michael Dykman wrote:

> I don't really use sets when I can avoid them (too much special
> wierdness) but from the manual I see this:
>
> mysql> INSERT INTO myset (col) VALUES
> -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
> Query OK, 5 rows affected (0.01 sec)
>
> mysql> SELECT col FROM myset;
> +------+
> | col |
> +------+
> | a,d |
> | a,d |
> | a,d |
> | a,d |
> | a,d |
> +------+
>
> which suggests your syntax is in error. It appears that it should be:
>
> update products set
> SKU="prodSKU2",
> Category="prodCat1",
> Name="name2",
> Title="title2",
> Description="descr",
> Price="22.55",
> SortFactor="500",
> Availability="1",
> OutOfStock="0",
> Weight="5.5",
> ShipFlatFee="10.0",
> ShipPercentPrice="5",
> ShipPercentWeight="2",
> sizes= 'Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge',
> colorsShadesNumbersShort='aqua:7FFFD4, blue:333399, gray:465945,
> navy-blue:CC7722, black:0000FF, maroon:B03060,
> purple:50404D,yellow:9ACD32, fuchsia:FF77FF'
> where ID="2";
>
> watch for the line-wraps in the SET data specifications.. I did both
> of your sets (I assume colorsShadesNumbersShort is a set.. I don't
> know what you are trying to do there.
>

No, that thew the same errors. I know you have to enclose the sets in
parentheses and individually quote each element.
V

>
> - michael dykman
>
> On Wed, Jan 6, 2010 at 9:32 AM, Victor Subervi
> wrote:
> > On Wed, Jan 6, 2010 at 10:23 AM, Michael Dykman
> wrote:
> >>
> >> What are the warnings?
> >>
> >> mysql> show warnings;
> >
> >
> +---------+------+------------------------------------------ ---------------------+
> > | Level | Code |
> > Message |
> >
> +---------+------+------------------------------------------ ---------------------+
> > | Warning | 1265 | Data truncated for column 'sizes' at row
> > 1 |
> > | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort'
> at
> > row 1 |
> >
> +---------+------+------------------------------------------ ---------------------+
> >
> >>
> >>
> >> On Wed, Jan 6, 2010 at 9:04 AM, Victor Subervi > >
> >> wrote:
> >> > Hi;
> >> > The following doesn't work with or without enclosing the sets in
> quotes:
> >> >
> >> > update products set SKU="prodSKU2", Category="prodCat1", Name="name2",
> >> > Title="title2", Description="descr", Price="22.55", SortFactor="500",
> >> > Availability="1", OutOfStock="0", Weight="5.5", ShipFlatFee="10.0",
> >> > ShipPercentPrice="5", ShipPercentWeight="2", sizes="('Extra-small',
> >> > 'Large',
> >> > 'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge')",
> >> > colorsShadesNumbersShort="('aqua:7FFFD4', 'blue:333399',
> 'gray:465945',
> >> > 'navy-blue:CC7722', 'black:0000FF', 'maroon:B03060', 'purple:50404D',
> >> > 'yellow:9ACD32', 'fuchsia:FF77FF')" where ID="2";
> >> >
> >> > Everything inserts but the sets which throw warnings. What am I
> missing?
> >> > TIA,
> >> > Victor
> >> >
> >> > --
> >> > The Logos has come to bear
> >> > http://logos.13gems.com/
> >> >
> >>
> >>
> >>
> >> --
> >> - michael dykman
> >> - mdykman@gmail.com
> >>
> >> May the Source be with you.
> >
> >
> >
> > --
> > The Logos has come to bear
> > http://logos.13gems.com/
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>



--
The Logos has come to bear
http://logos.13gems.com/

--000e0cd5cc72228329047c80f41b--

Re: Another Inserting Multiple Values with Set Problem

so you dropped the quotes around the unneccessarily bracketed
expression? It's hard to diagnose when your example isn't even what
you think is syntactically correct.

I can't find a reference, but i recall there is some limit on the
length of SET member identifiers.. some of yours look longish, maybe?

- md

On Wed, Jan 6, 2010 at 10:53 AM, Victor Subervi w=
rote:
> On Wed, Jan 6, 2010 at 10:56 AM, Michael Dykman wrote=
:
>
>> I don't really use sets when I can avoid them (too much special
>> wierdness) but from the manual I see this:
>>
>> mysql> INSERT INTO myset (col) VALUES
>> -> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
>> Query OK, 5 rows affected (0.01 sec)
>>
>> mysql> SELECT col FROM myset;
>> +------+
>> | col =A0|
>> +------+
>> | a,d =A0|
>> | a,d =A0|
>> | a,d =A0|
>> | a,d =A0|
>> | a,d =A0|
>> +------+
>>
>> which suggests your syntax is in error. =A0It appears that it should be:
>>
>> update products set
>> =A0SKU=3D"prodSKU2",
>> =A0Category=3D"prodCat1",
>> =A0Name=3D"name2",
>> =A0Title=3D"title2",
>> =A0Description=3D"descr",
>> =A0Price=3D"22.55",
>> =A0SortFactor=3D"500",
>> =A0Availability=3D"1",
>> =A0OutOfStock=3D"0",
>> =A0Weight=3D"5.5",
>> =A0ShipFlatFee=3D"10.0",
>> =A0ShipPercentPrice=3D"5",
>> =A0ShipPercentWeight=3D"2",
>> =A0sizes=3D 'Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarg=
e',
>> =A0colorsShadesNumbersShort=3D'aqua:7FFFD4, blue:333399, gray:465945,
>> navy-blue:CC7722, black:0000FF, maroon:B03060,
>> purple:50404D,yellow:9ACD32, fuchsia:FF77FF'
>> where ID=3D"2";
>>
>> watch for the line-wraps in the SET data specifications.. =A0I did both
>> of your sets (I assume colorsShadesNumbersShort is a set.. I don't
>> know what you are trying to do there.
>>
>
> No, that thew the same errors. I know you have to enclose the sets in
> parentheses and individually quote each element.
> V
>
>>
>> =A0- michael dykman
>>
>> On Wed, Jan 6, 2010 at 9:32 AM, Victor Subervi
>> wrote:
>> > On Wed, Jan 6, 2010 at 10:23 AM, Michael Dykman
>> wrote:
>> >>
>> >> What are the warnings?
>> >>
>> >> mysql> show warnings;
>> >
>> >
>> +---------+------+------------------------------------------ ------------=
---------+
>> > | Level =A0 | Code |
>> > Message =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
>> >
>> +---------+------+------------------------------------------ ------------=
---------+
>> > | Warning | 1265 | Data truncated for column 'sizes' at row
>> > 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
>> > | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort=
'
>> at
>> > row 1 |
>> >
>> +---------+------+------------------------------------------ ------------=
---------+
>> >
>> >>
>> >>
>> >> On Wed, Jan 6, 2010 at 9:04 AM, Victor Subervi om
>> >
>> >> wrote:
>> >> > Hi;
>> >> > The following doesn't work with or without enclosing the sets in
>> quotes:
>> >> >
>> >> > update products set SKU=3D"prodSKU2", Category=3D"prodCat1", Name=
=3D"name2",
>> >> > Title=3D"title2", Description=3D"descr", Price=3D"22.55", SortFacto=
r=3D"500",
>> >> > Availability=3D"1", OutOfStock=3D"0", Weight=3D"5.5", ShipFlatFee=
=3D"10.0",
>> >> > ShipPercentPrice=3D"5", ShipPercentWeight=3D"2", sizes=3D"('Extra-s=
mall',
>> >> > 'Large',
>> >> > 'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge')",
>> >> > colorsShadesNumbersShort=3D"('aqua:7FFFD4', 'blue:333399',
>> 'gray:465945',
>> >> > 'navy-blue:CC7722', 'black:0000FF', 'maroon:B03060', 'purple:50404D=
',
>> >> > 'yellow:9ACD32', 'fuchsia:FF77FF')" where ID=3D"2";
>> >> >
>> >> > Everything inserts but the sets which throw warnings. What am I
>> missing?
>> >> > TIA,
>> >> > Victor
>> >> >
>> >> > --
>> >> > The Logos has come to bear
>> >> > http://logos.13gems.com/
>> >> >
>> >>
>> >>
>> >>
>> >> --
>> >> =A0- michael dykman
>> >> =A0- mdykman@gmail.com
>> >>
>> >> =A0May the Source be with you.
>> >
>> >
>> >
>> > --
>> > The Logos has come to bear
>> > http://logos.13gems.com/
>> >
>>
>>
>>
>> --
>> =A0- michael dykman
>> =A0- mdykman@gmail.com
>>
>> =A0May the Source be with you.
>>
>
>
>
> --
> The Logos has come to bear
> http://logos.13gems.com/
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Another Inserting Multiple Values with Set Problem

--00163649a6f385aa73047c8198aa
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jan 6, 2010 at 12:14 PM, Michael Dykman wrote:

> so you dropped the quotes around the unneccessarily bracketed
> expression? It's hard to diagnose when your example isn't even what
> you think is syntactically correct.
>

Here's my example again. Syntactically correct. From my original post:

update products set SKU="prodSKU2", Category="prodCat1", Name="name2",
Title="title2", Description="descr", Price="22.55", SortFactor="500",
Availability="1", OutOfStock="0", Weight="5.5", ShipFlatFee="10.0",
ShipPercentPrice="5", ShipPercentWeight="2", sizes="('Extra-small', 'Large',
'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge')",
colorsShadesNumbersShort="('
aqua:7FFFD4', 'blue:333399', 'gray:465945', 'navy-blue:CC7722',
'black:0000FF', 'maroon:B03060', 'purple:50404D', 'yellow:9ACD32',
'fuchsia:FF77FF')" where ID="2";


>
> I can't find a reference, but i recall there is some limit on the
> length of SET member identifiers.. some of yours look longish, maybe?
>

They're not. Awaiting someone who can help me here.
TIA,
V

--00163649a6f385aa73047c8198aa--

Re: Another Inserting Multiple Values with Set Problem

--0015175ce094628584047c83159a
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jan 6, 2010 at 2:18 PM, Hassan Schroeder > wrote:

> On Wed, Jan 6, 2010 at 11:39 AM, Victor Subervi
> wrote:
>
> > Here's my example again. Syntactically correct. From my original post:
> >
> > update products set SKU="prodSKU2", Category="prodCat1", Name="name2",
> > Title="title2", Description="descr", Price="22.55", SortFactor="500",
> > Availability="1", OutOfStock="0", Weight="5.5", ShipFlatFee="10.0",
> > ShipPercentPrice="5", ShipPercentWeight="2", sizes="('Extra-small',
> 'Large',
> > 'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge')",
>
> Lose the enclosing double-quotes and put all the values within a
> single set of single quotes.
>
> > colorsShadesNumbersShort="('
> > aqua:7FFFD4', 'blue:333399', 'gray:465945', 'navy-blue:CC7722',
> > 'black:0000FF', 'maroon:B03060', 'purple:50404D', 'yellow:9ACD32',
> > 'fuchsia:FF77FF')"
>
> e.g. (shortened for lazyness):
>
> colorsShadesNumbersShort = ('aqua:7FFFD4, blue:333399')
>
> > where ID="2";
>
> mysql> update products set SKU="prodSKU2", Category="prodCat1",
Name="name2", Title="title2", Description="descr", Price="22.55",
SortFactor="500", Availability="1", OutOfStock="0", Weight="5.5",
ShipFlatFee="10.0", ShipPercentPrice="5", ShipPercentWeight="2",
sizes=('Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge'),
colorsShadesNumbersShort=('aqua:7FFFD4, blue:333399, gray:465945,
navy-blue:CC7722, black:0000FF, maroon:B03060, purple:50404D, yellow:9ACD32,
fuchsia:FF77FF') where ID="2";
Query OK, 1 row affected, 2 warnings (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 2

mysql> show warnings;
+---------+------+------------------------------------------ ---------------------+
| Level | Code |
Message |
+---------+------+------------------------------------------ ---------------------+
| Warning | 1265 | Data truncated for column 'sizes' at row
1 |
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+---------+------+------------------------------------------ ---------------------+
2 rows in set (0.00 sec)

Guess again.
V

--0015175ce094628584047c83159a--

Re: Another Inserting Multiple Values with Set Problem

--0016364173759b0a77047c8348f1
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jan 6, 2010 at 2:34 PM, Hassan Schroeder > wrote:

> On Wed, Jan 6, 2010 at 1:25 PM, Victor Subervi
> wrote:
>
> > mysql> update products set SKU="prodSKU2", Category="prodCat1",
> > Name="name2", Title="title2", Description="descr", Price="22.55",
> > SortFactor="500", Availability="1", OutOfStock="0", Weight="5.5",
> > ShipFlatFee="10.0", ShipPercentPrice="5", ShipPercentWeight="2",
> > sizes=('Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge'),
> > colorsShadesNumbersShort=('aqua:7FFFD4, blue:333399, gray:465945,
> > navy-blue:CC7722, black:0000FF, maroon:B03060, purple:50404D,
> yellow:9ACD32,
> > fuchsia:FF77FF') where ID="2";
> > Query OK, 1 row affected, 2 warnings (0.00 sec)
> > Rows matched: 1 Changed: 1 Warnings: 2
> >
> > mysql> show warnings;
> >
> +---------+------+------------------------------------------ ---------------------+
> > | Level | Code |
> > Message |
> >
> +---------+------+------------------------------------------ ---------------------+
> > | Warning | 1265 | Data truncated for column 'sizes' at row
> > 1 |
> > | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort'
> at
> > row 1 |
> >
> +---------+------+------------------------------------------ ---------------------+
> > 2 rows in set (0.00 sec)
> >
> > Guess again.
>
> Hardly a guess, that syntax works fine for me:
>
> mysql> update jeweleryproducts set audience = ('women,seniors') where
> product = 'E30123E';
> Query OK, 0 rows affected (0.00 sec)
> Rows matched: 1 Changed: 0 Warnings: 0
>
> mysql> select product, audience from jeweleryproducts where product =
> 'E30123E';
> +---------+---------------+
> | product | audience |
> +---------+---------------+
> | E30123E | women,seniors |
> +---------+---------------+
> 1 row in set (0.00 sec)
>
> mysql>
>

Works well *only* for one entry! Not multiple entries.

>
> What's the `create table` look like for the table in question?
>

mysql> describe products;
+--------------------------+-------------------------------- ------------------------------------------------------------ -------------------------------------------------------+---- --+-----+---------+----------------+
| Field |
Type
| Null | Key | Default | Extra |
+--------------------------+-------------------------------- ------------------------------------------------------------ -------------------------------------------------------+---- --+-----+---------+----------------+
| ID | tinyint(5)
unsigned
| NO | PRI | NULL | auto_increment |
| SKU |
varchar(40)
| NO | UNI | NULL | |
| Category |
varchar(40)
| YES | | NULL | |
| Name |
varchar(50)
| NO | | NULL | |
| Title |
varchar(100)
| NO | | NULL | |
| Description |
mediumtext
| NO | | NULL | |
| Price |
float(8,2)
| YES | | NULL | |
| SortFactor |
int(4)
| YES | | 500 | |
| Availability |
tinyint(1)
| NO | | 1 | |
| OutOfStock |
tinyint(1)
| NO | | 0 | |
| Weight |
float(7,2)
| NO | | 0.00 | |
| ShipFlatFee |
float(5,2)
| NO | | 10.00 | |
| ShipPercentPrice | tinyint(2)
unsigned
| NO | | 5 | |
| ShipPercentWeight | tinyint(2)
unsigned
| NO | | 2 | |
| pic0 |
mediumblob
| YES | | NULL | |
| pic1 |
mediumblob
| YES | | NULL | |
| sizes |
set('Extra-small','Small','Medium','Large','XLarge','XXLarge ','XXXLarge')
| YES | | NULL | |
| colorsShadesNumbersShort |
set('blue:333399','gray:465945','purple:50404D','navy-blue:C C7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black :0000FF','yellow:9ACD32')
| YES | | NULL | |
+--------------------------+-------------------------------- ------------------------------------------------------------ -------------------------------------------------------+---- --+-----+---------+----------------+
18 rows in set (0.00 sec)

V

--0016364173759b0a77047c8348f1--

Re: Another Inserting Multiple Values with Set Problem

How about you show us the schema for the table so we know what is
defined as what?

Also, as the update does succeed, it would be interesting to see what
value actually got stored. After you have accounted for each bit in
the stored value, we might have a clue about what is being truncated.

One thing I did just note: the hyphen in 'Extra-Small'. Set
identifiers need to be valid mysql identifiers and the hyphen '-' is
not a valid identifier character (as it is an arithmatic operator). I
can't imagine that those colons in the colour list are healthy either.

The point of a set identifier to be an easy mnemonic for a particular
bit value. Nothing is gained by trying to represent data with the
identifier itself.

- michael dykman


On Wed, Jan 6, 2010 at 1:25 PM, Victor Subervi wr=
ote:
> On Wed, Jan 6, 2010 at 2:18 PM, Hassan Schroeder com
>> wrote:
>
>> On Wed, Jan 6, 2010 at 11:39 AM, Victor Subervi >
>> wrote:
>>
>> > Here's my example again. Syntactically correct. From my original post:
>> >
>> > update products set SKU=3D"prodSKU2", Category=3D"prodCat1", Name=3D"n=
ame2",
>> > Title=3D"title2", Description=3D"descr", Price=3D"22.55", SortFactor=
=3D"500",
>> > Availability=3D"1", OutOfStock=3D"0", Weight=3D"5.5", ShipFlatFee=3D"1=
0.0",
>> > ShipPercentPrice=3D"5", ShipPercentWeight=3D"2", sizes=3D"('Extra-smal=
l',
>> 'Large',
>> > 'Small', 'Medium', 'XLarge', 'XXLarge', 'XXXLarge')",
>>
>> Lose the enclosing double-quotes and put all the values within a
>> single set of single quotes.
>>
>> > colorsShadesNumbersShort=3D"('
>> > aqua:7FFFD4', 'blue:333399', 'gray:465945', 'navy-blue:CC7722',
>> > 'black:0000FF', 'maroon:B03060', 'purple:50404D', 'yellow:9ACD32',
>> > 'fuchsia:FF77FF')"
>>
>> e.g. (shortened for lazyness):
>>
>> colorsShadesNumbersShort =3D ('aqua:7FFFD4, blue:333399')
>>
>> > where ID=3D"2";
>>
>> mysql> update products set SKU=3D"prodSKU2", Category=3D"prodCat1",
> Name=3D"name2", Title=3D"title2", Description=3D"descr", Price=3D"22.55",
> SortFactor=3D"500", Availability=3D"1", OutOfStock=3D"0", Weight=3D"5.5",
> ShipFlatFee=3D"10.0", ShipPercentPrice=3D"5", ShipPercentWeight=3D"2",
> sizes=3D('Extra-small, Large, Small, Medium, XLarge, XXLarge, XXXLarge'),
> colorsShadesNumbersShort=3D('aqua:7FFFD4, blue:333399, gray:465945,
> navy-blue:CC7722, black:0000FF, maroon:B03060, purple:50404D, yellow:9ACD=
32,
> fuchsia:FF77FF') where ID=3D"2";
> Query OK, 1 row affected, 2 warnings (0.00 sec)
> Rows matched: 1 =A0Changed: 1 =A0Warnings: 2
>
> mysql> show warnings;
> +---------+------+------------------------------------------ -------------=
--------+
> | Level =A0 | Code |
> Message =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
> +---------+------+------------------------------------------ -------------=
--------+
> | Warning | 1265 | Data truncated for column 'sizes' at row
> 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|
> | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' a=
t
> row 1 |
> +---------+------+------------------------------------------ -------------=
--------+
> 2 rows in set (0.00 sec)
>
> Guess again.
> V
>



--=20
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Another Inserting Multiple Values with Set Problem

--001636417335229ee6047c835b64
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Jan 6, 2010 at 2:40 PM, Michael Dykman wrote:

> How about you show us the schema for the table so we know what is
> defined as what?
>

Done in last email.

>
> Also, as the update does succeed, it would be interesting to see what
> value actually got stored. After you have accounted for each bit in
> the stored value, we might have a clue about what is being truncated.
>

mysql> select sizes, colorsShadesNumbersShort from products;
+-------------+--------------------------+
| sizes | colorsShadesNumbersShort |
+-------------+--------------------------+
| Extra-small | blue:333399 |
| Extra-small | aqua:7FFFD4 |
| Extra-small | blue:333399 |
| | |
+-------------+--------------------------+
4 rows in set (0.00 sec)

Ain't nothin' getting stored.

>
> One thing I did just note: the hyphen in 'Extra-Small'. Set
> identifiers need to be valid mysql identifiers and the hyphen '-' is
> not a valid identifier character (as it is an arithmatic operator). I
> can't imagine that those colons in the colour list are healthy either.
>

They all work except in a certain case where I had to pull the hyphen out. I
can enter all of these products individually.

>
> The point of a set identifier to be an easy mnemonic for a particular
> bit value. Nothing is gained by trying to represent data with the
> identifier itself.
>

Huh? Please explain.
V

--001636417335229ee6047c835b64--

Re: Another Inserting Multiple Values with Set Problem

mysql> create table t (sizes
set('Extra-small','Small','Medium','Large','XLarge','XXLarge ','XXXLarge'),
colorsShadesNumbersShort
set('blue:333399','gray:465945','purple:50404D','navy-blue:C C7722','fuchsia:FF77FF','aqua:7FFFD4','maroon:B03060','black :0000FF','yellow:9ACD32'));
Query OK, 0 rows affected
(0.00 sec)

mysql> insert into t values ('Large,Small', 'aqua:7FFFD4,fuchsia:FF77FF');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+-------------+----------------------------+
| sizes | colorsShadesNumbersShort |
+-------------+----------------------------+
| Small,Large | fuchsia:FF77FF,aqua:7FFFD4 |
+-------------+----------------------------+
1 row in set (0.01 sec)

mysql> update t set sizes=
'Extra-small,Large,Small,Medium,XLarge,XXLarge,XXXLarge',
colorsShadesNumbersShort=
'aqua:7FFFD4,blue:333399,gray:465945,navy-blue:CC7722,black: 0000FF,maroon:B03060,purple:50404D,yellow:9ACD32,fuchsia:FF7 7FF';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0


mysql> select * from t\G
*************************** 1. row ***************************
sizes:
Extra-small,Small,Medium,Large,XLarge,XXLarge,XXXLarge
colorsShadesNumbersShort:
blue:333399,gray:465945,purple:50404D,navy-blue:CC7722,fuchs ia:FF77FF,aqua:7FFFD4,maroon:B03060,black:0000FF,yellow:9ACD 32
1 row in set (0.00 sec)

/ Carsten

Victor Subervi skrev:
> On Wed, Jan 6, 2010 at 2:40 PM, Michael Dykman wrote:
>
>> How about you show us the schema for the table so we know what is
>> defined as what?
>>
>
> Done in last email.
>
>> Also, as the update does succeed, it would be interesting to see what
>> value actually got stored. After you have accounted for each bit in
>> the stored value, we might have a clue about what is being truncated.
>>
>
> mysql> select sizes, colorsShadesNumbersShort from products;
> +-------------+--------------------------+
> | sizes | colorsShadesNumbersShort |
> +-------------+--------------------------+
> | Extra-small | blue:333399 |
> | Extra-small | aqua:7FFFD4 |
> | Extra-small | blue:333399 |
> | | |
> +-------------+--------------------------+
> 4 rows in set (0.00 sec)
>
> Ain't nothin' getting stored.
>
>> One thing I did just note: the hyphen in 'Extra-Small'. Set
>> identifiers need to be valid mysql identifiers and the hyphen '-' is
>> not a valid identifier character (as it is an arithmatic operator). I
>> can't imagine that those colons in the colour list are healthy either.
>>
>
> They all work except in a certain case where I had to pull the hyphen out. I
> can enter all of these products individually.
>
>> The point of a set identifier to be an easy mnemonic for a particular
>> bit value. Nothing is gained by trying to represent data with the
>> identifier itself.
>>
>
> Huh? Please explain.
> V
>
>
> !DSPAM:451,4b44da73427881287616796!
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Another Inserting Multiple Values with Set Problem

--000e0cd6aa62754150047ca1af36
Content-Type: text/plain; charset=ISO-8859-1

OK, guys, I'm totally confused:

mysql> insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
"prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
"10", "5", "2", "('Small, Medium, XSmall')", "('teal_E2725B, black_0000FF,
olive_6B8E23, yellow_9ACD32')");
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------ ---------------------+
| Level | Code |
Message |
+---------+------+------------------------------------------ ---------------------+
| Warning | 1265 | Data truncated for column 'sizes' at row
1 |
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+---------+------+------------------------------------------ ---------------------+
2 rows in set (0.00 sec)

mysql> truncate products;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
"prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
"10", "5", "2", "('Small', 'Medium', 'XSmall')", "('teal_E2725B',
'black_0000FF', 'olive_6B8E23', 'yellow_9ACD32')");
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> truncate products;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
"prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
"10", "5", "2", "Small, Medium, XSmall", "teal_E2725B, black_0000FF,
olive_6B8E23, yellow_9ACD32");
Query OK, 1 row affected, 2 warnings (0.00 sec)

mysql> insert into products (sizes, colorsShadesNumbersShort) values
('Large, Small','aqua_7FFFD4, fuchsia_FF77FF');
Query OK, 1 row affected, 6 warnings (0.01 sec)

mysql> show warnings;
+---------+------+------------------------------------------ ---------------------+
| Level | Code |
Message |
+---------+------+------------------------------------------ ---------------------+
| Warning | 1364 | Field 'SKU' doesn't have a default
value |
| Warning | 1364 | Field 'Name' doesn't have a default
value |
| Warning | 1364 | Field 'Title' doesn't have a default
value |
| Warning | 1364 | Field 'Description' doesn't have a default
value |
| Warning | 1265 | Data truncated for column 'sizes' at row
1 |
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+---------+------+------------------------------------------ ---------------------+
6 rows in set (0.00 sec)

mysql> describe products sizes;
+-------+--------------------------------------------------- -------------------+------+-----+---------+-------+
| Field |
Type | Null
| Key | Default | Extra |
+-------+--------------------------------------------------- -------------------+------+-----+---------+-------+
| sizes |
set('XSmall','Small','Medium','Large','XLarge','XXLarge','XX XLarge') | YES
| | NULL | |
+-------+--------------------------------------------------- -------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> describe products colorsShadesNumbersShort;
+--------------------------+-------------------------------- ------------------------------------------------------------ -----------------------------------------------+------+----- +---------+-------+
| Field |
Type
| Null | Key | Default | Extra |
+--------------------------+-------------------------------- ------------------------------------------------------------ -----------------------------------------------+------+----- +---------+-------+
| colorsShadesNumbersShort |
set('black_0000FF','yellow_9ACD32','teal_E2725B','olive_6B8E 23','green_00A550','white_0F4D92','silver_708090','red_FE271 2','lime_32CD32')
| YES | | NULL | |
+--------------------------+-------------------------------- ------------------------------------------------------------ -----------------------------------------------+------+----- +---------+-------+
1 row in set (0.00 sec)

How, o how, do I insert into sets???
TIA,
V

--000e0cd6aa62754150047ca1af36--

Re: Another Inserting Multiple Values with Set Problem

--001636ed67b79362fa047ca21bf4
Content-Type: text/plain; charset=ISO-8859-1

Hi Victor,

You have given space after the comma(shown here: ('Small, Medium, XSmall')).
Take out the space and try, it will work.

Example:

mysql> insert into products(sizes) values ('Small,Medium,small,medium');
Query OK, 1 row affected (0.05 sec)

Regards,
Thiyaghu CK
www.mafiree.com

On Fri, Jan 8, 2010 at 12:26 PM, Victor Subervi wrote:

> OK, guys, I'm totally confused:
>
> mysql> insert into products (SKU, Category, Name, Title, Description,
> Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee,
> ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
> "10", "5", "2", "('Small, Medium, XSmall')", "('teal_E2725B, black_0000FF,
> olive_6B8E23, yellow_9ACD32')");
> Query OK, 1 row affected, 2 warnings (0.00 sec)
>
> mysql> show warnings;
>
> +---------+------+------------------------------------------ ---------------------+
> | Level | Code |
> Message |
>
> +---------+------+------------------------------------------ ---------------------+
> | Warning | 1265 | Data truncated for column 'sizes' at row
> 1 |
> | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
> row 1 |
>
> +---------+------+------------------------------------------ ---------------------+
> 2 rows in set (0.00 sec)
>
> mysql> truncate products;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into products (SKU, Category, Name, Title, Description,
> Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee,
> ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
> "10", "5", "2", "('Small', 'Medium', 'XSmall')", "('teal_E2725B',
> 'black_0000FF', 'olive_6B8E23', 'yellow_9ACD32')");
> Query OK, 1 row affected, 2 warnings (0.00 sec)
>
> mysql> truncate products;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into products (SKU, Category, Name, Title, Description,
> Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee,
> ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
> "10", "5", "2", "Small, Medium, XSmall", "teal_E2725B, black_0000FF,
> olive_6B8E23, yellow_9ACD32");
> Query OK, 1 row affected, 2 warnings (0.00 sec)
>
> mysql> insert into products (sizes, colorsShadesNumbersShort) values
> ('Large, Small','aqua_7FFFD4, fuchsia_FF77FF');
> Query OK, 1 row affected, 6 warnings (0.01 sec)
>
> mysql> show warnings;
>
> +---------+------+------------------------------------------ ---------------------+
> | Level | Code |
> Message |
>
> +---------+------+------------------------------------------ ---------------------+
> | Warning | 1364 | Field 'SKU' doesn't have a default
> value |
> | Warning | 1364 | Field 'Name' doesn't have a default
> value |
> | Warning | 1364 | Field 'Title' doesn't have a default
> value |
> | Warning | 1364 | Field 'Description' doesn't have a default
> value |
> | Warning | 1265 | Data truncated for column 'sizes' at row
> 1 |
> | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
> row 1 |
>
> +---------+------+------------------------------------------ ---------------------+
> 6 rows in set (0.00 sec)
>
> mysql> describe products sizes;
>
> +-------+--------------------------------------------------- -------------------+------+-----+---------+-------+
> | Field |
> Type | Null
> | Key | Default | Extra |
>
> +-------+--------------------------------------------------- -------------------+------+-----+---------+-------+
> | sizes |
> set('XSmall','Small','Medium','Large','XLarge','XXLarge','XX XLarge') | YES
> | | NULL | |
>
> +-------+--------------------------------------------------- -------------------+------+-----+---------+-------+
> 1 row in set (0.00 sec)
>
> mysql> describe products colorsShadesNumbersShort;
>
> +--------------------------+-------------------------------- ------------------------------------------------------------ -----------------------------------------------+------+----- +---------+-------+
> | Field |
> Type
> | Null | Key | Default | Extra |
>
> +--------------------------+-------------------------------- ------------------------------------------------------------ -----------------------------------------------+------+----- +---------+-------+
> | colorsShadesNumbersShort |
>
> set('black_0000FF','yellow_9ACD32','teal_E2725B','olive_6B8E 23','green_00A550','white_0F4D92','silver_708090','red_FE271 2','lime_32CD32')
> | YES | | NULL | |
>
> +--------------------------+-------------------------------- ------------------------------------------------------------ -----------------------------------------------+------+----- +---------+-------+
> 1 row in set (0.00 sec)
>
> How, o how, do I insert into sets???
> TIA,
> V
>

--001636ed67b79362fa047ca21bf4--

Re: Another Inserting Multiple Values with Set Problem

--001636426b55e3e791047ca35fc3
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Jan 8, 2010 at 2:26 AM, Thiyaghu CK wrote:

> Hi Victor,
>
> You have given space after the comma(shown here: ('Small, Medium,
> XSmall')). Take out the space and try, it will work.
>
> Example:
>
> mysql> insert into products(sizes) values ('Small,Medium,small,medium');
> Query OK, 1 row affected (0.05 sec)
>

insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values("prodSKU1",
"prodCat1", "name1", "title1", "descr", "123.45", "500", "1", "0", "2.5",
"10.00", "5", "2", "('Small,Medium,XSmall')",
"('teal_E2725B,black_0000FF,yellow_9ACD32')");

mysql> select sizes from products;
+--------+
| sizes |
+--------+
| Medium |
+--------+
1 row in set (0.00 sec)

mysql> select colorsShadesNumbersShort from products;
+--------------------------+
| colorsShadesNumbersShort |
+--------------------------+
| black_0000FF |
+--------------------------+
1 row in set (0.00 sec)


It only entered one of each!! Please help.
V

--001636426b55e3e791047ca35fc3--

Re: Another Inserting Multiple Values with Set Problem

--001636c9285e6a35de047ca72e33
Content-Type: text/plain; charset=ISO-8859-1

Hi Victor,

Take out the double quotes from[ "('Small,Medium,XSmall')",
"('teal_E2725B,black_0000FF,yellow_9ACD32')"]. Its working fine for me.

mysql> insert into products (sizes, colorsShadesNumbersShort)
values(('Small,Medium,XSmall'), ('teal_E2725B,black_0000FF,yellow_9ACD32'));
Query OK, 1 row affected (0.05 sec)

mysql> select * from products;
+---------------------+------------------------------------- ---+
| sizes | colorsShadesNumbersShort |
+---------------------+------------------------------------- ---+
| XSmall,Small,Medium | black_0000FF,yellow_9ACD32,teal_E2725B |
+---------------------+------------------------------------- ---+
5 rows in set (0.01 sec)

Regards,
Thiyaghu CK
www.mafiree.com

On Fri, Jan 8, 2010 at 2:27 PM, Victor Subervi wrote:

> On Fri, Jan 8, 2010 at 2:26 AM, Thiyaghu CK wrote:
>
>> Hi Victor,
>>
>> You have given space after the comma(shown here: ('Small, Medium,
>> XSmall')). Take out the space and try, it will work.
>>
>> Example:
>>
>> mysql> insert into products(sizes) values ('Small,Medium,small,medium');
>> Query OK, 1 row affected (0.05 sec)
>>
>
> insert into products (SKU, Category, Name, Title, Description, Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "123.45", "500", "1", "0", "2.5",
> "10.00", "5", "2", "('Small,Medium,XSmall')",
> "('teal_E2725B,black_0000FF,yellow_9ACD32')");
>
> mysql> select sizes from products;
> +--------+
> | sizes |
> +--------+
> | Medium |
> +--------+
>
> 1 row in set (0.00 sec)
>
> mysql> select colorsShadesNumbersShort from products;
> +--------------------------+
> | colorsShadesNumbersShort |
> +--------------------------+
> | black_0000FF |
> +--------------------------+
>
> 1 row in set (0.00 sec)
>
>
> It only entered one of each!! Please help.
> V
>

--001636c9285e6a35de047ca72e33--

Re: Another Inserting Multiple Values with Set Problem

--000e0cd6aa621a3da2047ca745fc
Content-Type: text/plain; charset=ISO-8859-1

On Fri, Jan 8, 2010 at 9:29 AM, Thiyaghu CK wrote:

> Hi Victor,
>
> Take out the double quotes from[ "('Small,Medium,XSmall')",
> "('teal_E2725B,black_0000FF,yellow_9ACD32')"]. Its working fine for me.
>
> mysql> insert into products (sizes, colorsShadesNumbersShort)
> values(('Small,Medium,XSmall'), ('teal_E2725B,black_0000FF,yellow_9ACD32'));
>
> Query OK, 1 row affected (0.05 sec)
>

Yes, that worked! Thanks!
V

--000e0cd6aa621a3da2047ca745fc--