MySql Insert query having a select statement for a value not working

Question

I have an insert query where one value needs to be a pipe separated string returned from a select statement. Select statement and insert both work perfect independently but mySql gives syntax error when both queries are merged.

This is my Insert query

INSERT INTO sales (site_id, name, start_date, end_date, per_item_discount, enabled,entry_ids) 
VALUES (1, 'auto', 1448662620, 1448749020, 2.0000, 1,
        (SELECT GROUP_CONCAT(DISTINCT entry_id SEPARATOR '|') 
         FROM data
         JOIN relationships ON data.entry_id = relationships.parent_id
         WHERE (relationships.child_id = 18 AND data.id_5 = 'VHK-SG') ))

Now both these queries individually , select statement and Insert with hard coded entry_ids like '1|2' , works perfectly but combination is not.

I am not sure what am I missing here.

Any help is appreciated.

Why would you want to store a GROUP_CONCATenated result? (Hint: You wouldn't!) — Strawberry, Nov 26 '15 at 14:28
I need concanated stuff because it is a 1->n mapping between sales and the items id on which this sale get applied. All items' id related to sale are stored as pipe separated string. — Neeraj Sharma, Nov 27 '15 at 12:09

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

You need to change your syntax. Check this out.

You should do something like:

INSERT INTO sales 
(site_id,name,start_date,end_date,per_item_discount,enabled,entry_ids) 
SELECT 1,'auto',1448662620,1448749020,2.0000,1,GROUP_CONCAT(DISTINCT entry_id SEPARATOR '|')
FROM data JOIN relationships ON data.entry_id = relationships.parent_id 
WHERE (relationships.child_id = 18 AND data.id_5 = 'VHK-SG')

edited May 23 '17 at 12:33

Community

1
1

answered Nov 26 '15 at 14:29

Alessandro Ruffolo

1,575
1
16
34

This works just inserting 2 rows in single queries but accepting it as it removed syntex error. – Neeraj Sharma Nov 27 '15 at 12:11

MySql Insert query having a select statement for a value not working

1 Answers1