hi there, hope in your help.
I’ve this two tables in mysql database:
mysql> SELECT
Nm,
zone,
sID,
ID
FROM
myTable;
+-------+------+-----+-----+
| Nm | zone | sID | ID |
+-------+------+-----+-----+
| AAA | XY5N | 10 | 1 |
| BBB | XY5N | 10 | 2 |
| CCC | XY5N | 10 | 3 |
| DDD | XY5N | 10 | 4 |
| EEE | XY5N | 10 | 5 |
| FFF | XY5N | 10 | 6 |
| GGG | XY5N | 10 | 7 |
+-------+------+-----+-----+
7 rows in set
mysql> SELECT
Nm,
zone,
ID
FROM
myTableCopy;
+-------+------+----+
| Nm | zone | id |
+-------+------+----+
| NULL | XY5N | 10 |
+-------+------+----+
1 row in set
I need update the field Nm of table ‘myTableCopy’ appending all values of tabel ‘myTable’ where the same ‘zone’.
I tried this query, don’t have error but the update not working, can you help me please?
thank you in advance.
the output:
+------------------------------------+------+----+
| Nm | zone | id |
+------------------------------------+------+----+
| AAA; BBB; CCC; DDD; EEE; FFF; GGG | XY5N | 10 |
+------------------------------------+------+----+
the query:
UPDATE myTableCopy t1
JOIN (
SELECT
Nm,
zone
FROM
myTable
) AS t2 ON t1.Zone = t2.Zone
SET t1.Nm = CONCAT(t1.Nm, ';', t2.Nm);
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
In your case (?):
mysql> SELECT zone,
-> GROUP_CONCAT(DISTINCT zone
-> ORDER BY zone DESC SEPARATOR '; ')
-> FROM myTable
-> GROUP BY zone;
r937
3
you are on the right track with GROUP_CONCAT
however, the example you gave is wrong
if you do GROUP BY zone, then each distinct zone will produce one row in the result
so the GROUP_CONCAT of that zone will have just one value
Hello Rudy!
You have right… definitely 
mysql> SELECT
zone,
GROUP_CONCAT(
DISTINCT Nm
ORDER BY
Nm ASC SEPARATOR '; '
) AS concat_nm
FROM
`dotables`
GROUP BY
zone;
+------+-----------------------------------+
| zone | concat_nm |
+------+-----------------------------------+
| XY5N | AAA; BBB; CCC; DDD; EEE; FFF; GGG |
+------+-----------------------------------+
1 row in set
-- ----------------------------
-- Table structure for `dotables`
-- ----------------------------
DROP TABLE IF EXISTS `dotables`;
CREATE TABLE `dotables` (
`Nm` varchar(255) DEFAULT NULL,
`zone` varchar(255) DEFAULT NULL,
`sID` int(10) DEFAULT NULL,
`ID` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of dotables
-- ----------------------------
INSERT INTO `dotables` VALUES ('AAA', 'XY5N', '10', '1');
INSERT INTO `dotables` VALUES ('BBB', 'XY5N', '10', '2');
INSERT INTO `dotables` VALUES ('CCC', 'XY5N', '10', '3');
INSERT INTO `dotables` VALUES ('DDD', 'XY5N', '10', '4');
INSERT INTO `dotables` VALUES ('EEE', 'XY5N', '10', '5');
INSERT INTO `dotables` VALUES ('FFF', 'XY5N', '10', '6');
INSERT INTO `dotables` VALUES ('GGG', 'XY5N', '10', '7');