Neeraj Sharma Neeraj Sharma - 2 months ago 9
MySQL Question

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

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.

Answer

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')