如何基于表JOIN结果向starMappings执行INSERT操作？

我数据库中有两张表：

SELECT starId, starName, starQuandrant, starCategory 
FROM astro1.starList

SELECT firstLoc, secondLoc 
FROM astro1.starMappings

starMappings是映射表，其中firstLoc和secondLoc均为starId。

通过以下语句可获取特定starCategory下两张表的关联关系：

SELECT 
    sl.starId, sl.starName, sl.starQuandrant, sl.starCategory, 
    sm.firstLoc, sm.secondLoc
FROM 
    astro1.starList sl
INNER JOIN 
    astro1.starMappings sm ON sl.starId = sm.firstLoc
WHERE 
    starCategory = 'A01'

查询结果：

starId | starName    | starQuandrant | starCategory | firstLoc | secondLoc
01997  | Beta Persei | 01-02003      |     A01      | 01997    | 767762
01999  | HR 6618     | 02-04441      |     A01      | 01999    | 889885

此处sm.firstLoc与starId始终相等。

starList表中新增了B01类别：

SELECT starId, starName, starQuandrant, starCategory 
FROM astro1.starList
WHERE starCategory = 'B01'

该类别下的starId和starCategory与A01不同，但starName和starQuandrant与A01完全匹配，目前starMappings中无B01的关联记录。

我需要基于starQuandrant，让B01类别的关联关系与A01一致：将B01的starId作为firstLoc，对应的secondLoc取A01同象限的secondLoc值，插入到astro1.starMappings中。预期插入后的关联结果如下：

starId | starName    | starQuandrant | starCategory | firstLoc | secondLoc
03771  | Beta Persei | 01-02003      |     B01      | 03771    | 767762
03982  | HR 6618     | 02-04441      |     B01      | 03982    | 889885

我尝试了以下INSERT语句，但无法正确获取sl2的starId和sm的secondLoc：

INSERT INTO astro1.starMappings(firstLoc, secondLoc)
    SELECT sl2.starId, sm.secondLoc 
    FROM astro1.starList sl1
    INNER JOIN astro1.starList sl2 ON sl1.starQuandrant = sl2.starQuandrant
    INNER JOIN astro1.starMappings sm ON sl1.id = sm.firstLoc
    WHERE sl1.starCategory = A01
      AND sl2.starCategory = B01

请问有可行的实现方法吗？

你的语句存在两处问题：一是关联条件里的sl1.id为错误字段，应该是sl1.starId；二是可优化关联逻辑，确保精准匹配对应象限的映射值。

正确的INSERT语句如下：

INSERT INTO astro1.starMappings(firstLoc, secondLoc)
SELECT 
    sl2.starId AS firstLoc,
    sm.secondLoc
FROM 
    astro1.starList sl1
-- 关联A01和B01中同象限的记录
INNER JOIN astro1.starList sl2 
    ON sl1.starQuandrant = sl2.starQuandrant
    AND sl1.starCategory = 'A01'
    AND sl2.starCategory = 'B01'
-- 关联A01对应的映射记录
INNER JOIN astro1.starMappings sm 
    ON sl1.starId = sm.firstLoc;

说明 #

1. 修正字段错误：将sl1.id改为sl1.starId，你的表中主键是starId而非id。
2. 合并筛选条件：把类别筛选逻辑放到JOIN子句中，让关联逻辑更清晰。
3. 精准匹配映射：确保每条B01记录都能对应到同象限A01的secondLoc值，完成映射插入。

如果要避免重复插入（后续执行时不会添加已有记录），可添加NOT EXISTS条件：

INSERT INTO astro1.starMappings(firstLoc, secondLoc)
SELECT 
    sl2.starId AS firstLoc,
    sm.secondLoc
FROM 
    astro1.starList sl1
INNER JOIN astro1.starList sl2 
    ON sl1.starQuandrant = sl2.starQuandrant
    AND sl1.starCategory = 'A01'
    AND sl2.starCategory = 'B01'
INNER JOIN astro1.starMappings sm 
    ON sl1.starId = sm.firstLoc
WHERE NOT EXISTS (
    SELECT 1 
    FROM astro1.starMappings sm_exists 
    WHERE sm_exists.firstLoc = sl2.starId
);

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