Data wont insert into table using insert into and right join

Question

I'm trying to insert the result of a Right join into column CATO_NAME of table deal_classification_DM I created. The result of the select is what I expect and the program run's fine, but data is not updated into the table. What's wrong?

`INSERT INTO deal_classification_DM
(CATO_NAME)
SELECT
    taxonomies.NAME
FROM 
    taxonomies
RIGHT JOIN 
    deal_classification_DM ON taxonomies.ID = deal_classification_DM.CATO_ID;

The insertion is done in table deal_classification_DM which I created like this:

CREATE TABLE deal_classification_DM 
AS SELECT 
    deal_taxonomy.DEAL_ID
    ,deal_taxonomy.TAXONOMY_ID AS TAXO_ID
    ,taxonomies.NAME AS TAXO_NAME
    ,taxonomies.PARENT_ID AS CATO_ID 
FROM
    deal_taxonomy
LEFT JOIN
    taxonomies ON deal_taxonomy.TAXONOMY_ID = taxonomies.ID;

ALTER TABLE deal_classification_DM
ADD COLUMN 
    CATO_NAME TEXT;

Answer 1

What you really need to do is update and not insert, e.g.:

UPDATE deal_classification_DM d
SET d.CATO_NAME = (
    SELECT name FROM taxonomies WHERE ID = d.CATO_ID
);

INSERT statement will insert new rows with null values in DEAL_ID, TAXO_ID and CATO_ID columns whereas you only need to update the values of CATO_NAME column as it's a new column.