SQL Insert into with join and where

I have three tables. For example:

1. newTable:

   config_id  | name
   12         | null                    
   22         | null                   
   

2. oldTable:

   oldTable_id | name   | value
   1           | Jack   | 10                  
   2           | Frank  | 22         
   

3. associativeTable:

   config_id    | oldTable_id
   12           | 1                    
   22           | 2   
   

What I want to do now is: I want to move the name column from oldTable to newTable. I have already altered newTable and added an empty name column. Now I’m trying to write a correct INSERT INTO statement. What I have so far is:

INSERT INTO newTable (name)
SELECT name
FROM oldTable ot join associativeTable at on  ot.oldTable_id = at.oldTable_id
WHERE at.config_id = newTable.config_id;

I’m a bit lost on it. I was writing INSERT INTO statements before but never when I had an associative table. How would a correct statement look for my case?

Answer

A way to do it would be using a subquery in the SET part:

UPDATE newtable n
   SET n.name = (SELECT name 
                   FROM oldtable o 
                   JOIN associativetable a 
                     ON o.oldtable_id = a.oldtable_id 
                    AND a.config_id = n.config_id  
                );

See db<>fiddle