mysql - SQL通过从同一个表中选择插入多个值和父ID

Question

如何通过从同一个表中选择来插入多个值和父 ID

我在下面尝试并收到错误

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 说：文档 #1093 - 表 'ctg_tbl' 被指定了两次，既作为 'INSERT' 的目标，又作为数据的单独源

标签： mysqlsql

Answer 1

只需将查询转换为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';