如何使用来自另一个表的值插入表中 - mysql内连接 [英] How to insert into table with values from another tables - mysql inner join

问题描述

在这种情况下我创建了2个表：



项目：

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')

现在我想将数据插入模块表中，但不是ID_PROJECT。我希望从projects表中插入PROJECT_NAME。当我尝试使用命令时（在你有什么尝试？）中它显示错误：

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 - 字段列表中的未知列'projects.PROJECT_NAME'

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

但是当我这样写的时候（第二个代码）在你有什么尝试？）它没有显示任何错误，但它没有将任何行记录插入模块表。



我有一个问题是可以在内部连接另一个表（内部联接项目表）中插入模块表值吗？如果是的话告诉我该怎么做。 Thx任何帮助。



例如：



在模块中我没有插入任何东西。但是想要插入数据：

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';

它没有显示任何错误，但它没有向modules表插入任何行记录。

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

推荐答案

你无法将数据插入到不存在的列中。



modules 表中没有名为 PROJECT_NAME ，因此您无法在该列中插入任何内容。



ID_PROJECT 列需要一个数字。项目名称不是数字。您无法将项目名称插入该字段。



两个命令都尝试加入模块表。但该表不包含任何行，因此不会插入任何行。您需要更新您对SQL如何工作的理解：

SQL连接的可视化表示 [ ^ ]



您的命令应如下所示：



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:

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

这篇关于如何使用来自另一个表的值插入表中 - mysql内连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！