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

Question

1.00/5 (1 vote)

See more:

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 command( in "What have you tried?") it shew an error:

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

But when i wrote like that (the second code in "What have you tried?") 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)

What I have tried:

I've tried first with that 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';

But it shew an error. But when i wrote in below command:

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.

Posted 20-May-19 20:22pm

Member 10696161

Updated 21-May-19 3:29am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-05-21T03:29:00

You cannot insert data into a column which doesn't exist.

The modules table doesn't have a column called PROJECT_NAME, so you cannot insert anything into that column.

The ID_PROJECT column expects a number. The project name is not a number. You cannot insert the project name into that field.

Both commands attempt to join to the modules table. But that table doesn't contain any rows, so there will be no rows to insert. You need to refresh your understanding of how SQL joins work:
Visual Representation of SQL Joins[^]

Your command should look something like this:

SQL

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