How do I insert multiple values and parent id by selecting from same table
I tried below and getting an error
INSERT IGNORE INTO ctg_tbl (`ctg_name`,`ctg_img`,`ctg_parent_id`) VALUES ('Gravy', 'GravyImg', (SELECT id FROM ctg_tbl WHERE ctg_name='Foods')), ('Curry', 'CurryImg', (SELECT id FROM ctg_tbl WHERE ctg_name='Foods')), ('Ball', 'BallImg', (SELECT id FROM ctg_tbl WHERE ctg_name='Games'))
MySQL said: Documentation #1093 – Table ‘ctg_tbl’ is specified twice, both as a target for ‘INSERT’ and as a separate source for data
Advertisement
Answer
Your query can be made to work by simply converting it to an INSERT INTO ... SELECT
:
INSERT IGNORE INTO ctg_tbl (ctg_name, ctg_img, ctg_parent_id) SELECT 'Gravy', 'GravyImg', id FROM ctg_tbl WHERE ctg_name = 'Foods' UNION ALL SELECT 'Curry', 'CurryImg', id FROM ctg_tbl WHERE ctg_name = 'Foods' UNION ALL SELECT 'Ball', 'BallImg', id FROM ctg_tbl WHERE ctg_name = 'Games';