MYSQL使用两个不同表的结果插入到单个表中

Hello to whomever wishes to bite his teeth on this,

你好，谁愿意咬牙切齿，

I came across multiple examples of how to use the result set of subquery select into another table. What I have however is:

我遇到了多个如何将子查询结果集选择到另一个表中的示例。然而，我有：

SELECT `klanten_zakelijk`.`bedrijfs_id`, `klanten`.`klant_id` 
    FROM `klanten`,`klanten_zakelijk`
    WHERE `klanten`.`emailadres` = '$email' 
    AND `klanten_zakelijk`.`bedrijfs_kvk`='$kvknr'

As you can see I take the results from two different tables and need to port them to another table.

如您所见，我从两个不同的表中获取结果，并需要将它们移植到另一个表中。

I wish to insert those values into the table klant_bedrijf_machtiging.

我希望将这些值插入到表klant_bedrijf_machtiging中。

INSERT INTO `klant_bedrijf_machtiging` (`klant_id`, `bedrijfs_id`, `machtiging`) VALUES ('8501', '1', '3');

The column machtiging is is standard 3 with an insert but I need to be able to change that as well to 1 if the company already exists, but I will intercept that at another part of my code.

列标准是带有插入的标准3，但是如果公司已经存在，我需要能够将其更改为1，但是我将在我的代码的另一部分拦截它。

All the examples I saw were just the results from one table being imported into anther table. Does that same logic apply when you take results from two tables? If so, what would be the wisest and most efficient way to implement this?

我看到的所有示例都只是将一个表导入到anther表中的结果。从两个表中获取结果时是否也适用相同的逻辑？如果是这样，那么实现这一目标最明智，最有效的方法是什么？

So to make the question short: How do I get bedrijfs_id and klant_id into the corresponding columns into klant_bedrijf_machtiging whilst still myself being able to manipulate the last column machtiging in just 1 query.

因此，为了简化问题：如何将bedrijfs_id和klant_id放入klant_bedrijf_machtiging的相应列中，同时我仍然可以在1个查询中操作最后一列的机器人。

4 个解决方案

#1

Good news! MySQL has an easy way to do this: MySQL Insert Into w/ Select

好消息！ MySQL有一个简单的方法：MySQL插入w / Select

So in your case it would be:

所以在你的情况下它将是：

INSERT INTO klant_bedrijf_machtiging (`klant_id`, `bedrijfs_id`, `machtiging`)
SELECT `klanten`.`klant_id`, `klanten_zakelijk`.`bedrijfs_id`, 3
    FROM `klanten`,`klanten_zakelijk`
    WHERE `klanten`.`emailadres` = '$email' 
    AND `klanten_zakelijk`.`bedrijfs_kvk`='$kvknr'

As for changing the item to 1 if it already exists, I am not sure how you would do that in one query without making a huge mess of it.

至于如果已经存在将项目更改为1，我不确定如何在一个查询中执行此操作而不会造成大量混乱。

#2

INSERT INTO `klant_bedrijf_machtiging` (`klant_id`, `bedrijfs_id`, `machtiging`)
(SELECT `klanten`.`klant_id`, `klanten_zakelijk`.`bedrijfs_id`, 3
FROM `klanten`,`klanten_zakelijk`
WHERE `klanten`.`emailadres` = '$email'
AND `klanten_zakelijk`.`bedrijfs_kvk`='$kvknr')

#3

INSERT INTO yourtable (fields) SELECT `klanten_zakelijk`.`bedrijfs_id`, `klanten`.`klant_id` 
    FROM `klanten`,`klanten_zakelijk`
    WHERE `klanten`.`emailadres` = '$email' 
    AND `klanten_zakelijk`.`bedrijfs_kvk`='$kvknr'

#4

-1

INSERT INTO klant_bedrijf_machtiging (`klant_id`, `bedrijfs_id`, `machtiging`)
SELECT `klanten`.`klant_id`, `klanten_zakelijk`.`bedrijfs_id`, 3
    FROM `klanten`,`klanten_zakelijk`
    WHERE `klanten`.`emailadres` = '$email' 
    AND `klanten_zakelijk`.`bedrijfs_kvk`='$kvknr'

#1