I'm sorry if this has been asked before, I've gone through tens of questions and none of them solved my issue.

Here is an example of my Data table: Table1

So here, we have the program Acrobat in the category PDF, Photoshop in the category graphics, After Effects in the category video. I have a table for categories and a table for programs:

CREATE TABLE categories
(
cat_id int AUTO_INCREMENT,
category varchar(255),
PRIMARY KEY (cat_id)
);

  CREATE TABLE programs
(
    program_id int AUTO_INCREMENT,
    program varchar(255),
    cat_id int,
    PRIMARY KEY (program_id),
    FOREIGN KEY (cat_id) REFERENCES categories(cat_id)
);

ALTER TABLE programs AUTO_INCREMENT=100;

My problem is, I need to link the cat_id from the programs table to the program column in Table 1. Essentially, I need:

Table1: program -> category

categories: category -> cat_id

programs: program -> cat_id

What sort of join (or other operation) do I need to do in order to complete the programs table? Right now, the cat_id column is all "null".

I've tried linking the categories table to Table1 (hoping it would simplify the join with the programs table) and all sorts of combinations of INSERT INTO programs... INNER JOIN on ... but I'm either getting "ambiguous" errors or other errors. Nothing has worked.

CodePudding user response：

Is that what you want :

You can copy only some columns from one table into another table:

insert into programs(program, cat_id)
select t.program, c.cat_id
from table1 t
inner join categories c on c.category = t.category

Demo here