Search code examples
mysqlinner-joinsql-insertinsert-into

How to insert into table with values from another tables - mysql inner join

In this case i have created 2 tables:

Projects:

CREATE TABLE projects (
ID int(5) NOT NULL,
PROJECT_NAME varchar(50) NOT NULL
);

Modules:

CREATE TABLE modules (
ID_MODULE int(11) NOT NULL,
NAME varchar(50),
ID_PROJECT int(11) DEFAULT NULL
);

I've inserted data to projects table:

INSERT INTO projects (ID, PROJECT_NAME) VALUES
(1, 'BOX'),
(2, 'Duck')

Now I'd like to insert data into "modules" table, but not with ID_PROJECT. I want insert with PROJECT_NAME from "projects" table. When i tried with bellow command:

INSERT INTO modules (ID_MODULE, NAME, projects.PROJECT_NAME)
SELECT 1, 'S-BOX', projects.PROJECT_NAME
FROM modules INNER JOIN projects
ON modules.ID_PROJECT = projects.ID AND projects.PROJECT_NAME = 'BOX';

It shew an error:

1054 - Unknown column 'projects.PROJECT_NAME' in field list

But when i wrote like this:

INSERT INTO modules (ID_MODULE, NAME, ID_PROJECT)
SELECT 1, 'S-BOX', projects.PROJECT_NAME
FROM modules INNER JOIN projects
ON modules.ID_PROJECT = projects.ID AND projects.PROJECT_NAME = 'BOX';

It didn't show any error, but it didn't insert any row record to "modules" table.

I have a question is it possible to insert into "modules" table values with inner join another table (inner join "projects" table)? If yes tell what should i do. Thx for any help.

For example:

In "modules" table i haven't insert anything. But want insert data:

ID_MODULE = 1
NAME = "S-BOX"
projects.PROJECT_NAME = "BOX" (which is inner joined on modules.ID_PROJECT = projects.ID)
Solution

  • 1) Your first query is incorrect, especially this part INSERT INTO modules (ID_MODULE, NAME, --> projects.PROJECT_NAME). You cannot define another column from another different table than the one that you want to insert into.

    2) Your second query is correct. However, if according to your example here, there will be no data inserted since your query do INNER JOIN with modules table that is empty. You can try an insert without doing INNER JOIN like below:

    INSERT INTO modules (ID_MODULE, NAME, ID_PROJECT)
SELECT 1, 'S-BOX', projects.PROJECT_NAME
FROM projects
WHERE projects.PROJECT_NAME='BOX';

    I reckon that the query above will work but you will see this message:

    Warning Code : 1265
Data truncated for column 'ID_PROJECT' at row 1

    Because if you look at your column datatype for projects.PROJECT_NAME it's varchar(50) but modules.ID_PROJECT it's int(11). Basically, when you look at the inserted data in modules.ID_PROJECT, you will see 0 (or maybe NULL) instead of the original value from projects.PROJECT_NAME = 'BOX'. Therefore, you need to make sure your column datatype matches to retain the original data.