I have a MySQL GROUP_CONCAT .... INSERT issue This code works:
SELECT group_concat(tabel2.img_name separator ',')
FROM tabel2
GROUP BY tabel2.produit_id
I need to insert the result into another table and I'm stuck.
This (or any combination I could think of) doesn't work
INSERT INTO tabel1.imgname
SELECT group_concat(tabel2.img_name separator ',')
FROM tabel2
GROUP BY tabel2.produit_id
WHERE tabel1.product_id = tabel2.produit_id
What am I doing wrong?
CREATE TABLE IF NOT EXISTS `tabel1` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`rubrique_id` int(11) NOT NULL,
`marque_id` int(11) NOT NULL,
`subfamily_id` int(11) NOT NULL,
`product_name` varchar(150) NOT NULL,
`imgname` varchar(255) DEFAULT NULL,
`product_description1` text NOT NULL,
`product_description2` text NOT NULL,
`product_order` int(11) NOT NULL,
`product_page` int(11) NOT NULL,
`price_min` float NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;
INSERT INTO `tabel1` (`product_id`, `rubrique_id`, `marque_id`, `subfamily_id`, `product_name`, `imgname`, `product_description1`, `product_description2`, `product_order`, `product_page`, `price_min`)
VALUES
(33, 15, 23, 40, 'product 1', NULL, '', '', 0, 0, 0),
(34, 13, 13, 13, 'product 2', NULL, '', '', 0, 0, 0),
(35, 14, 14, 14, 'product 3', NULL, '', '', 0, 0, 0);
CREATE TABLE IF NOT EXISTS `tabel2` (
`img_id` int(11) NOT NULL AUTO_INCREMENT,
`img_name` text NOT NULL,
`article_id` int(11) DEFAULT NULL,
`produit_id` int(11) DEFAULT NULL,
`product_select` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`img_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4018 ;
INSERT INTO `tabel2` (`img_id`, `img_name`, `article_id`, `produit_id`, `product_select`)
VALUES
(4013, 'acoacki086050.jpg', 13342, 33, NULL),
(4014, '32252il6jh2dqex.jpg', NULL, 34, NULL),
(4015, '33265ulrzmgr18w.jpg', NULL, 34, NULL),
(4016, '40334zcfk0c4n67.jpg', NULL, 35, NULL),
(4017, '40473frd4900u82.jpg', NULL, 35, NULL);
So for say tabel1.product_id = 34 I need to have tabel1.imgname = 32252il6jh2dqex.jpg,33265ulrzmgr18w.jpg
What you describe is UPDATE
. not INSERT
:
UPDATE
tabel1 AS t1
JOIN
( SELECT produit_id
, GROUP_CONCAT(img_name SEPARATOR ',') AS grp_img_name
FROM tabel2
GROUP BY produit_id
) AS t2
ON t2.produit_id = t1.product_id
SET
t1.imgname = t2.grp_img_name ;
Random rants:
Why, why, why do you have these table1
, tabel1
, tabel2
, tableX
names? Names of any object (of tables, columns, indexes, constraints, databases) should reflect usage.
table1
says nothing.
tabel1
says nothing except that you don't spell-check your names.
It's good practice that columns used in Foreign Key constraints (and as a result for joining), have identical names (if possible). Not imgname
in one table and img_name
in another. Not produit_id
in one and product_id
in the other. It helps you, the next programmer and the guys who try to help you over SO, to not make mistakes writing ON t2.produit_id = t1.produit_id
. It can also help, if you like the USING (product_id)
syntax instead of the ON
syntax for joins.
(update)
It seems that the different names are because the tables come from different sources/databases and it was not your choice. I would suggest you alter them to have uniform/sensible names, if you have a lot of work to do with them. If it's a one-time job, just to transfer/transform some data, don't bother.