CODEWITHSUNDEEP
mysql
Rasiel
Rasiel

Reputation: 2913

Is there a way to combine two mysql fields that are of the "set" type?

Is there a way to combine two fields... both of the set type?

field1 is ('c')
field2 is ('a')

I want to select the field so that it returns 'a,c'

I've tried concat, but it returns duplicates in the set. so if field 2 was ('a','c') it would return 'a,c,c'

I've also tried field1 & field2, but because this is a set it returns the decimal value.

Upvotes: 0

Views: 52

Answers (1)

miken32
miken32

Reputation: 42717

Try field1 | field2 instead of field1 & field2

mysql> create table test (id serial, foo set('a','b','c') not null, bar set('a','b','c') not null);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test (foo,bar) values('a,c','c,b'),('b,a','a'),('a,b','b,c');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test;
+----+-----+-----+
| id | foo | bar |
+----+-----+-----+
|  1 | a,c | b,c |
|  2 | a,b | a   |
|  3 | a,b | b,c |
+----+-----+-----+
3 rows in set (0.01 sec)

mysql> update test set foo = foo | bar;
Query OK, 2 rows affected (0.16 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> select * from test;
+----+-------+-----+
| id | foo   | bar |
+----+-------+-----+
|  1 | a,b,c | b,c |
|  2 | a,b   | a   |
|  3 | a,b,c | b,c |
+----+-------+-----+
3 rows in set (0.00 sec)

Upvotes: 1

Related Questions