如何向同一MySQL表插入多条数据并通过查询该表获取父ID？

Ah, I recognize that error—MySQL has a strict rule against referencing the table you're inserting into directly in a subquery within the VALUES clause. That's why your current statement is throwing the #1093 "Table specified twice" error. Let's walk through two solid solutions to fix this:

Solution 1: Use INSERT ... SELECT with a Derived Table #

Instead of using VALUES, structure your query to select from a temporary derived table of your new categories, then join with ctg_tbl to fetch the parent IDs. This avoids the direct reference issue entirely:

INSERT IGNORE INTO ctg_tbl (`ctg_name`, `ctg_img`, `ctg_parent_id`)
SELECT 
    new_data.ctg_name,
    new_data.ctg_img,
    parent.id
FROM (
    -- Define your new category data here
    SELECT 'Gravy' AS ctg_name, 'GravyImg' AS ctg_img, 'Foods' AS parent_name UNION ALL
    SELECT 'Curry', 'CurryImg', 'Foods' UNION ALL
    SELECT 'Ball', 'BallImg', 'Games'
) AS new_data
JOIN ctg_tbl AS parent ON parent.ctg_name = new_data.parent_name;

This works because we first create a temporary set of your new entries, then join to pull in the parent IDs—no direct reference to the target table in the subquery tied to the insert operation.

Solution 2: Pre-fetch Parent IDs into Variables #

If you only have a small number of parent categories, you can fetch their IDs first and store them in variables, then use those variables in your INSERT statement. This is straightforward and easy to read:

-- Fetch parent IDs into variables first
SET @foods_id = (SELECT id FROM ctg_tbl WHERE ctg_name = 'Foods');
SET @games_id = (SELECT id FROM ctg_tbl WHERE ctg_name = 'Games');

-- Insert using the pre-fetched IDs
INSERT IGNORE INTO ctg_tbl (`ctg_name`, `ctg_img`, `ctg_parent_id`)
VALUES 
    ('Gravy', 'GravyImg', @foods_id),
    ('Curry', 'CurryImg', @foods_id),
    ('Ball', 'BallImg', @games_id);

Just double-check that the parent records (Foods and Games) exist in ctg_tbl before running this—otherwise the variables will be NULL, which could cause issues if ctg_parent_id is a non-nullable column.

内容的提问来源于stack exchange，提问作者Sujay U N