How can I do an INSERT based on result of a table JOIN?-CodePudding

I have these two tables in my database:

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

SELECT firstLoc, secondLoc 
FROM astro1.starMappings

starMappings is just a mapping table where firstLoc and secondLoc are starIds

I can get the relationship between starList and starMappings for a starCategory like this:

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

And the results would be:

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

Here, sm.firstLoc and starId will always be equal.

Well, I have another new category in starList like this:

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

It's starIds and starCategory are different, but the starName and starQuandrant are the same as A01.

But it doesn't yet have any relationship entries in starMappings

However, I want them to be equal to starCategory = A01 based on the starQuandrant.

My question is, how can I insert all entries from :

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

into astro.starMappings based on the starQuandrant?

So for example, 2 entries for starCategory B01 might look like:

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

So after inserting into astro.starMappings , firstLoc is different, but secondLoc is the same as it was above for A01

So what I need is the starId from the B01 category and the secondLoc value when I do the JOIN with starMappings using the A01 category.

I tried writing this insert query:

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

But I can't figure out how to select the starId of sl2 and the secondLoc of sm(starMappings) to insert into the starMappings table.

Is there a way to do this?

Thanks!

CodePudding user response：

INSERT INTO astro1.starMappings(firstLoc, secondLoc)
SELECT 
    sl.starID,a.secondLoc
FROM 
    astro1.starList sl
INNER JOIN astro1.starMappings sma ON sl.starId = sma.firstLoc
CROSS JOIN
(SELECT 
    sma.secondLoc
FROM 
    astro1.starList sl
INNER JOIN astro1.starMappings sma ON sl.starId = sma.firstLoc
WHERE 
    sl.starCategory = 'A01') a
WHERE 
    sl.starCategory = 'B01'