如何编写INSERT INTO语句，插入时关联其他表查询名称？

Hey there! Let's figure out how to insert your target data into Table4, where we pull the nameFromID value by matching the input ID against Table1, Table2, and Table3. I'll walk you through a few practical methods based on different scenarios you might be dealing with.

First, let's assume we're working with these table structures to keep things clear:

• Table1/Table2/Table3: Each has id INT and name VARCHAR(50)
• Table4: Needs id INT, nameFromID VARCHAR(50), plus any other columns you're inserting

Method 1: Prioritize Tables with COALESCE #

If your IDs won't appear in more than one table, or you want to prioritize pulling names from Table1 first, then Table2, then Table3, this is the simplest approach.

INSERT INTO Table4 (id, nameFromID, other_column1, other_column2)
SELECT 
    input.id,
    -- Grab the first non-null name from the tables in order
    COALESCE(t1.name, t2.name, t3.name, 'Unknown') AS nameFromID,
    input.other_column1,
    input.other_column2
FROM (
    -- Replace this with your actual input data (could be a temp table or another source)
    VALUES 
        (1, 'sample_val1', 'sample_val2'),
        (2, 'sample_val3', 'sample_val4'),
        (3, 'sample_val5', 'sample_val6')
) AS input(id, other_column1, other_column2)
LEFT JOIN Table1 t1 ON input.id = t1.id
LEFT JOIN Table2 t2 ON input.id = t2.id
LEFT JOIN Table3 t3 ON input.id = t3.id;

How this works: #

• COALESCE returns the first non-null value it finds, so it checks Table1 first, then falls back to Table2, then Table3.
• I added 'Unknown' as a default if the ID doesn't exist in any table—feel free to remove that or swap it for your own default value.

Method 2: Combine Tables with UNION ALL #

If you need to aggregate all possible ID-name pairs from the three tables first (and handle potential duplicates), use this method.

INSERT INTO Table4 (id, nameFromID, other_column1, other_column2)
SELECT 
    input.id,
    combined.name AS nameFromID,
    input.other_column1,
    input.other_column2
FROM (
    -- Your input data here
    VALUES 
        (1, 'sample_val1', 'sample_val2'),
        (4, 'sample_val7', 'sample_val8')
) AS input(id, other_column1, other_column2)
LEFT JOIN (
    -- Merge all ID-name pairs from the three tables
    SELECT id, name FROM Table1
    UNION ALL
    SELECT id, name FROM Table2
    UNION ALL
    SELECT id, name FROM Table3
    -- If IDs might repeat across tables and you want to avoid duplicates, use UNION instead of UNION ALL
    -- Or group by ID to pick a single name, like:
    -- SELECT id, MAX(name) AS name FROM (...) GROUP BY id
) AS combined ON input.id = combined.id;

Heads up: #

• If the same ID exists in multiple tables, UNION ALL will return multiple rows for that ID, which could cause duplicate inserts into Table4 (especially if id is a primary key). Use UNION to auto-remove duplicates (only if the names are identical) or add a GROUP BY to pick one name per ID.

Method 3: Explicit Checks with CASE Statements #

If you need to explicitly verify which table an ID exists in (maybe IDs are partitioned across tables by range), use a CASE statement for precise control.

INSERT INTO Table4 (id, nameFromID, other_column1, other_column2)
SELECT 
    input.id,
    CASE
        WHEN EXISTS(SELECT 1 FROM Table1 WHERE id = input.id) THEN (SELECT name FROM Table1 WHERE id = input.id)
        WHEN EXISTS(SELECT 1 FROM Table2 WHERE id = input.id) THEN (SELECT name FROM Table2 WHERE id = input.id)
        WHEN EXISTS(SELECT 1 FROM Table3 WHERE id = input.id) THEN (SELECT name FROM Table3 WHERE id = input.id)
        ELSE 'ID Not Found'
    END AS nameFromID,
    input.other_column1,
    input.other_column2
FROM (
    -- Your input data
    VALUES 
        (1, 'sample_val1', 'sample_val2'),
        (5, 'sample_val9', 'sample_val10')
) AS input(id, other_column1, other_column2);

How this works: #

• It checks each table in order—if the ID exists in Table1, it grabs that name; if not, moves to Table2, etc. If no match is found, it returns your custom default message.

内容的提问来源于stack exchange，提问作者StealthRT